MySQL Complex查询无法返回正确的结果

Question

I have the following query:

'SELECT * FROM posts LEFT JOIN taxi ON taxi.taxiID = posts.postID
    WHERE (taxi.value = 1 AND taxi.userID ='.$userID.') 
    AND ??????????????
    ORDER BY taxi.ID DESC
    LIMIT 10'

The way the site works is the user can tag posts as being "liked". When a post is liked, the taxi table is given a new row with taxiID being the same as postID, userID to store the user that liked the post, and value which is set to 1. Disliking a post sets value to 0.

I want to display all posts where value is 1 and userID is $userID - check. However, I also want the query to display all the posts where the user hasn't liked a post yet. Thing is, if the user hasn't liked a post yet, userID is NULL with a corresponding value of NULL. If I query for that, I'll be skipping those posts that other users have liked but the user hasn't.

Here's the taxi table:

ID    taxiID    userID    value
1     1         1         1
2     1         6         1
3     1         4         0
4     2         1         0
5     2         6         1
6     2         4         0
7     3         6         1
8     3         4         0

Assuming $userID is 1, my query ought to display taxiID 1 and 3 (because user 1 liked ID 1 AND hasn't liked or disliked taxiID 3.

The code I've posted will only result in displaying taxiID 1.

The question is, what is my missing line in my query supposed to be given the above?

Answer 1

It seems you want to find all taxiID that the use has not disliked:

SELECT taxiID 
FROM taxi
GROUP BY taxiID
HAVING taxiID NOT IN 
      ( SELECT taxiID
        FROM taxi 
        WHERE userID = '.$userID.'
          AND value = 0
      )
ORDER BY taxiID DESC
LIMIT 10

Test in SQL-Fiddle

---

You probably have a post table, so it would be better to use:

SELECT *                               --- whatever columns from `post` table
FROM post
WHERE postID NOT IN                     
      ( SELECT taxiID
        FROM taxi 
        WHERE userID = '.$userID.'
          AND value = 0
      )
ORDER BY postID 
LIMIT 10

---

If that taxi.taxiID means postID, then you should rename it to taxi.postID. It's very confusing as it is.