dongliufa6380
dongliufa6380
2012-07-15 13:53

MySQL / PHP:如果value不为0,则使用inner join

已采纳

I want to use a select query which gets data from tables.

It looks like this

SELECT table1.value1, table1.value2, table1.id, table2.id, table2.value2
FROM table1
INNER JOIN table2
ON table2.id = table1.value4
ORDER BY table1.id DESC
LIMIT 10

Though table1.value4 can sometimes be 0 and there are no table2.id with the value 0 and since it's a auto-increment value, it starts at 1. And I want it to start at 1.

Because when it's equal to 0 then that specific row isn't available, just the other ones.

But I would like to some how set a where clause that it only should get the table2 values if table1.value4 isn't equal to 0.

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

2条回答

  • doushi8231 doushi8231 9年前

    What you actually want, it seems, is a LEFT JOIN then. All rows from table1 will be returned, even if there is no match in table2 (as with table1.value4 = 0).

    SELECT table1.value1, table1.value2, table1.id, table2.id, table2.value2
    FROM
      table1
      LEFT JOIN table2
        ON table2.id = table1.value4
    ORDER BY table1.id DESC
    LIMIT 10
    
    点赞 评论 复制链接分享
  • dongshun5963 dongshun5963 9年前

    TRy this::

    If you dont have 0 in table2 id, the below query works fine::

    SELECT table1.value1, table1.value2, table1.id, table2.id, table2.value2
    FROM table1
    INNER JOIN table2
    ON table2.id = table1.value4
    ORDER BY table1.id DESC
    LIMIT 10
    

    But there are some values in table2 having id 0 and you want that to be ignored then

    SELECT table1.value1, table1.value2, table1.id, table2.id, table2.value2
    FROM table1
    INNER JOIN table2
    ON (table2.id = table1.value4 and table1.id!=0)
    ORDER BY table1.id DESC
    LIMIT 10
    
    点赞 评论 复制链接分享

相关推荐