Bypassing subselects

[LaundroMat]

I'm so used to subselects that my mind boggles trying to find solutions to bypass them.

Suppose I have these tables:
[list=a]
Candidate (id, name, category_id)
Category (id, title)
Votes (id, user_id, candidate_id)
[/list=a]
Now I'd like to query my database for all candidates, get their category and show the total (sum) of votes they received.

[Kiwi]

SELECT
Candidate.name,
Category.title,
COUNT(Votes.id)
FROM
Candidate AS can,
Category AS cat,
Votes AS v
WHERE
can.category_id = cat.id AND
can.id = v.candidate_id
GROUP BY
v.candidate_id
I haven't tested that, but it's the basic idea. You may need to do a proper left join to get it to function correctly, but that shouldn't be necessary.

[LaundroMat]

The only thing I had to do (and that I find slightly weird) is (marked in red)

Code:
SELECT
can.lname,
cat.title,
COUNT(v.id)
FROM
nominee AS can,
category AS cat,
votes AS v
WHERE
can.category = cat.id AND
can.id = v.nominee_id
GROUP BY
v.nominee_id;


(You'll see I changed the table names too, but never mind that).

Now I'll have a look at how to include those with zero votes on their names too... I'm thinking UNION?

/edit
Nah, UNION is a problem again. I'd like to UNION with a SELECT WHERE can.id NOT IN (another SELECT). No can do, ofcourse...

/ edit 2:

Code:
SELECT
can.id,
can.lname,
cat.title,
COUNT(v.id) AS pop
FROM
nominee AS can,
category AS cat,
votes AS v
WHERE
can.category = cat.id AND
can.id = v.nominee_id
GROUP BY
v.nominee_id
UNION
SELECT
can.id,
can.lname,
cat.title,
0 AS pop
FROM
nominee AS can,
category AS cat
LEFT JOIN
votes AS v
ON
v.nominee_id = NULL
WHERE
can.category = cat.id
GROUP BY
can.id

Now I get those with zero votes, but the ones with votes return with a "pop" of zero too... I'm getting there though.

EDIT 3

Got it! (Just thought I'd share it with you people). I should've made it into a left join, as you said already... *slaps forehead*. Thus:

Code:
SELECT
can.id,
can.lname,
cat.title,
COUNT(v.id) AS pop
FROM
nominee AS can,
category AS cat
LEFT JOIN
votes AS v
ON
v.nominee_id = can.id
WHERE
can.category = cat.id
GROUP BY
can.id