virtual_odin Posted October 29, 2006 Share Posted October 29, 2006 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] Link to comment https://forums.phpfreaks.com/topic/25518-join-and-count-question/ Share on other sites More sharing options...
virtual_odin Posted October 29, 2006 Author Share Posted October 29, 2006 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] Link to comment https://forums.phpfreaks.com/topic/25518-join-and-count-question/#findComment-116453 Share on other sites More sharing options...
Recommended Posts