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

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 <lists@kikobu.com> 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/mysql?unsub=wultsch@gmail.com
>
>