Jump to content

Archived

This topic is now archived and is closed to further replies.

virtual_odin

Join and count question

Recommended Posts

OK I have two tables (cars and reviews). In cars are details of cars and in reviews comments on [u]some[/u] of those cars.  All the records in cars are unique. There will be between zero and many comments in reviews relative to each row of cars.  I would like, in one query, to extract rows of the data in cars (and a calculation I needn't trouble you with) and how many times (ie COUNT) there is a row in reviews that matches an identified row in cars.  By creating a dummy row in reviews for every row in cars, the query below works.  But that clearly is not efficient.  So there must be a way to do it without the dummy entries...  Please?  As always your advice gratefully received.
[code]
SELECT
cars.*,
[calc] AS km,
COUNT(reviews.id) AS ct
FROM cars, reviews
WHERE cars.id = reviews.establishment_id
GROUP BY cars.id
ORDER BY [function]
[/code]

Share this post


Link to post
Share on other sites
Two more trial and error attempts and I solved it...

"SELECT
cars.*,
        [calc] AS km
COUNT(reviews.id) AS ct
FROM cars LEFT JOIN reviews
ON cars.id = reviews.cars_id
GROUP BY CARS.id
ORDER BY [function]

Share this post


Link to post
Share on other sites
Guest
This topic is now closed to further replies.

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.