Wednesday, April 18, 2012

MySQL Complex query not returning right results

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 ??????????????

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?

No comments:

Post a Comment