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]
