Query help

[ToonMariner]

Hi everyone,

 

Having  a spot of bother with a join query...

 

I have 3 tables media, user and media votes and one of the parameters i need to return is the number of votes an item in teh media table has - this is determined by the number of records in teh media_vote table.

 

I have the following query...

SELECT
`media`.*	,
`user`.`username`	,
DATEDIFF(NOW(), `media`.`added`)	AS `period`	,
IF (`media`.`rating` > 0, COUNT(`media_votes`.`vote_id`), 0)		AS `vote_no`
FROM
`media`	,
`user`	,
`media_votes`
WHERE
`media`.`flag`	!=	'm'
AND
`user`.`user_id`	=	`media`.`user_id`
AND
`media`.`categories` NOT IN (21, 40)
AND
`user`.`active`	=	'y'
AND
`media_votes`.`media_id`	=	`media`.`media_id`
GROUP BY
`media_votes`.`media_id`
ORDER BY
`media`.`added` DESC

 

all is dandy EXCEPT when teh item has no votes in the media_vote table.

 

IF (`media`.`rating` > 0, COUNT(`media_votes`.`vote_id`), 0) AS `vote_no`

 

was my feeble attempt to get round this but its clearly the

 

`media_votes`.`media_id` = `media`.`media_id`

 

portion which is giving me the headache and I just can't think how to get round it.

 

Any help would be very much appreciated...

[Barand]

You need to use a LEFT JOIN on the votes table

 

http://www.phpfreaks.com/tutorial/data-joins-unions

[ToonMariner]

you don't know how long it is since I've formulated a query!!! (about 2 months now!)

 

Thanks buddy - needed the refresher A LOT!