MySQL Complex query not returning right results

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?

taxi.ID 3 has taxi.value set to 0, so it wouldn't be returned. — Jake
– Jake, Commented Apr 18, 2012 at 23:47
Jake, I'm looking to return taxi.ID 3 because userID 1 did not set any value for it. Only Users 4 and 6 have set values. — Sweepster
– Sweepster, Commented Apr 18, 2012 at 23:48
It sounds to me like you just want to show all posts - those the user has liked, and those the user hasn't liked. What am I misunderstanding? — Jake
– Jake, Commented Apr 18, 2012 at 23:52

ypercubeᵀᴹ · Accepted Answer · 2012-04-19 00:35:47Z

3

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.

edited Apr 19, 2012 at 0:35

answered Apr 18, 2012 at 23:55

ypercubeᵀᴹ

116k19 gold badges181 silver badges249 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

ypercubeᵀᴹ Over a year ago

Does it return taxiID = 2 ?

Sweepster Over a year ago

It returns absolutely everything.

Sweepster Over a year ago

It returns taxiID 1,2,3,4,5,6,7,8. I want only taxiID 1 and 3 (assuming userID is 1)

ypercubeᵀᴹ Over a year ago

Try the link to the test. You probably use taxi.ID somewhere instead of my taxiID

Sweepster Over a year ago

You're right. it was the limit that was stopping it from showing more results. There's still the question of what about showing the posts that aren't in the table at all (ex: taxiID 4 which no user has liked or disliked at all and as such, doesn't appear in the table)?

|

Jake · Accepted Answer · 2012-04-19 01:08:51Z

1

I had this as a comment, but I think it's my final answer:

SELECT * FROM posts 
LEFT JOIN taxi ON taxi.taxiID = posts.postID
WHERE (taxi.value != 0 AND taxi.userID ='.$userID.') 
OR taxi.value is null
ORDER BY taxi.ID DESC
LIMIT 10

Ok, this is what should happen with the above query:

Get all posts
Add the info from the taxi table, and associate taxi data with post data by postID
Only show if taxi.value isn't 0 (meaning it can be null or 1) AND if the userID is the same as our variable

edited Apr 19, 2012 at 1:08

answered Apr 19, 2012 at 0:37

Jake

4,26410 gold badges38 silver badges60 bronze badges

2 Comments

Sweepster Over a year ago

I updated my query to show that taxi is subserviant to posts. I need to show all posts that user 1 liked AND all posts where user 1 did not like and did not dislike. Likes and dislikes are stored in taxi.

Jake Over a year ago

Ok, edited it one more time. If that doesn't work, I don't know.

lafor · Accepted Answer · 2012-04-19 00:01:32Z

0

Assuming taxi.taxiID is the same as post.ID and you're looking to select all posts not tagged by a user yet, try:

SELECT * FROM post WHERE ID NOT IN
(SELECT taxiID FROM taxi WHERE userID = 1)

this is for user with an ID=1 of course.

answered Apr 19, 2012 at 0:01

lafor

12.8k4 gold badges34 silver badges36 bronze badges

Collectives™ on Stack Overflow

MySQL Complex query not returning right results

3 Answers 3

11 Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

11 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related