Jump to content


Photo

Join and count question


  • This topic is locked This topic is locked
1 reply to this topic

#1 virtual_odin

virtual_odin
  • Members
  • PipPipPip
  • Advanced Member
  • 47 posts
  • LocationLondon, England

Posted 29 October 2006 - 08:35 PM

OK I have two tables (cars and reviews). In cars are details of cars and in reviews comments on some 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.
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]


#2 virtual_odin

virtual_odin
  • Members
  • PipPipPip
  • Advanced Member
  • 47 posts
  • LocationLondon, England

Posted 29 October 2006 - 08:44 PM

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]




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users