Used a JOIN, but ...

[teken8]

I have two tables to be joined(?):

ballots Table        votes Table
_____________       ________________________
|  id       |       | user_id  | ballot_id  |
------------|       |----------+------------|
|  1        |       |  2       |  1         |
|  2        |       |  1       |  1         |
+-----------+       |  2       |  2         |
                    +----------+------------+

The query

SELECT Ballot.id,Vote.user_id
FROM ballots as Ballot
LEFT JOIN votes as Vote
ON Ballot.id=Vote.ballot_id
WHERE (Vote.user_id='1' OR Vote.user_id IS NULL)

this returns:

___________________________
| Ballot.id | Vote.user_id |
------------+--------------|
|  1        | 1            |
+--------------------------+

but I want it to return:

___________________________
| Ballot.id | Vote.user_id |
------------+--------------|
|  1        | 1            |
|  2        | NULL         |
+--------------------------+

How can I do this? I see that joins won't work.

 

[PHV]

SELECT B.id, V.user_id
FROM ballots AS B
LEFT JOIN votes AS V
ON B.id=V.ballot_id AND V.user_id='1'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[teken8]

ahh! Thanks, I don't know why I didn't think of that. That worked consummately!