0

Not sure if my title explains it very well so here's an example. I have two MYSQL tables, comments and votes_comments which are matched based on a field comment_id. For sample data comments has two rows and votes_comments has one row which matches one of the comments row. AKA Two comments were written and a single person voted on one of them Given that, the following query returns 2 rows:

SELECT comments.*, users.username FROM comments 
JOIN users ON users.user_id = comments.user_id 
WHERE comments.track_id=6 AND comments.parent_id=-1
GROUP BY comments.comment_id

Now what I want to do is join on votes_comments which may or may not have a matching row. What I want to do is grab the matching value if it exists or return null in the column if there is no matching result. AKA: Did the user vote it up, down or not at all (null). Here is my modified query:

SELECT comments.*, users.username, votes_comments.vote FROM comments 
JOIN users ON users.user_id = comments.user_id 
LEFT JOIN votes_comments ON votes_comments.comment_id = comments.comment_id
WHERE track_id=6 AND parent_id=-1 AND votes_comments.user_id=1
GROUP BY comments.comment_id

This query only returns one row since I only have a single row in votes_comments. I thought that left join would return results the way I want but apparently not. Any ideas how to get what I'm looking for?

Andrew.S
  • 169
  • 12
  • It's not clear what result you are trying to get. Could you write a text output of the tables on which you do an example query, and the table you expect to be returned? – Jim DeLaHunt Apr 10 '12 at 07:48

2 Answers2

1

I suspect the WHERE clause AND votes_comments.user_id=1 is causing the problem.

Read a tutorial on Joins and you'll see that the result of your LEFT JOIN is a temporary table with two rows, and for each row, fields from comments JOIN users and from votes_comments. If no matching record from votes_comments exists, the fields in the temporary table are filled with NULL values.

Your WHERE clause, AND votes_comments.user_id=1, will fail because votes_comments.user_id is NULL in the rows of user with no matching votes_comments record.

If what you actually want is to join to the row in votes_comments what matches a specific user ID (e.g. ID=1), then your ON clause should be something like 

ON votes_comments.comment_id = comments.comment_id
    AND votes_comments.user_id=1
Jim DeLaHunt
  • 10,960
  • 3
  • 45
  • 74
  • Hmm, alright that makes sense. Removing that WHERE clause seems to be yielding both results. I'm still not sure how I can achieve the result I want though because lets say another user voted on the other comment. I would now get all both results and the vote value for each which are from two different users. Ideally what I'm looking for is all of the fields from the comment plus one additional column which represents a particular user_id's vote value (1, -1 or null) if that makes sense. – Andrew.S Apr 10 '12 at 06:54
  • I've extended my answer to respond to your modified question, given in the comment above. – Jim DeLaHunt Apr 10 '12 at 07:07
  • Your update brings me back to the original query I was using in my post which only yields one result. What I need is for it to return two results, one of which has a "vote" column of 1 which is the one the user voted on and the other row has a "vote" value of null because the user hasn't voted on that one. – Andrew.S Apr 10 '12 at 07:19
  • My update puts the `AND votes_comments.user_id=1` into the `ON` expression. Your original question put it into the `WHERE` expression. – Jim DeLaHunt Apr 10 '12 at 07:54
  • Oohhh. I see. That works perfect thanks :) I wasn't aware you could even do that. – Andrew.S Apr 10 '12 at 15:05
0

If you want to have rows from both of the tables you might consider using FULL OUTER JOIN. Like this:

SELECT comments.*, users.username, votes_comments.vote FROM comments 
JOIN users ON users.user_id = comments.user_id 
FULL OUTER JOIN votes_comments ON votes_comments.comment_id = comments.comment_id
AND votes_comments.user_id=1
WHERE track_id=6 AND parent_id=-1 
GROUP BY comments.comment_id

Edit

Sorry about that. You can sumulate a FULL OUTER JOIN read more about it here and here

Community
  • 1
  • 1
Arion
  • 31,011
  • 10
  • 70
  • 88
  • From what I'm reading it sounds like MYSQL doesn't support FULL OUTER JOIN but there are ways to simulate it. Looking into this now. – Andrew.S Apr 10 '12 at 06:38