Perhaps I am missing something, but why not do this as an inner join? SELECT man .name, GROUP_CONCAT( car. make ) AS 'makes' FROM man INNER JOIN car ON man. id = car.owner_id GROUP BY man .id
On my box this query is about 3X faster. I think Mysql is known for not performing well with (especially correlated) subselects... On 10/20/07, Morten <[EMAIL PROTECTED]> wrote: > > > Hi, I have the below setup: > > --A man can have multiple cars > > CREATE TABLE man (id INTEGER, name VARCHAR(64)); > CREATE TABLE car (id INTEGER, owner_id INTEGER, make VARCHAR(64)); > > --Add data > > INSERT INTO man VALUES (1, 'John'); > INSERT INTO car VALUES (1, 1, 'Mazda'); > INSERT INTO car VALUES (2, 1, 'Ford'); > INSERT INTO car VALUES (3, 1, 'Toyota'); > > --I want a single record for the result, containing owner name and > --the makes of all the cars he owns > > SELECT man.name, virtual_cars.makes > FROM man, (SELECT owner_id, GROUP_CONCAT(car.make) AS makes > FROM car GROUP BY owner_id) AS virtual_cars > WHERE man.id = virtual_cars.owner_id > > > Now, I'm concerned that MySQL will actually build the entire > virtual_cars view which will be quite expensive, so I'd like to hear if > it's somehow possible to join on man.id within the SELECT declaring the > view? > > Any tips for improving the query are much appreciated, > > Morten > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > >
