Hi guys,
I have 2 tables "cars" and "parts" where car has many parts. I need a
query to return some fields from the cars table as well as a field
from multiple parts records. I've come to the following approaches,
and would like to understand which is the better, and why, or if
there's a 3rd and even better approach:
Approach 1 - subselects:
SELECT cars.id,
(SELECT parts.value FROM parts WHERE car_id = cars.id AND id =
3) AS part_3,
(SELECT parts.value FROM parts WHERE car_id = cars.id AND id =
4) AS part_4
FROM cars
WHERE id = 2;
Approach 2 - joins:
SELECT cars.id, parts_3.value AS part_3, parts_4.value AS part_4
FROM cars
INNER JOIN parts AS parts_3 ON cars.id = parts_3.car_id
INNER JOIN parts AS parts_4 ON cars.id = parts_4.car_id
WHERE parts_3.id = 3
AND parts_4.id = 4
AND cars.id = 2;
I've tried to find out if joins are preferred over subselects, but am
not able to come to a definite conclusion. I read that correlated
subselects are bad, and I should go for the join, but I know the id of
the record in the outer query and can hard code that into the
subselect (if that makes a difference).
The EXPLAIN result only differs in that the select_type is SIMPLE in
the JOIN approach, but PRIMARY/DEPENDENT SUBQUERY in the subselect
approach.
Any tips much appreciated, the full example below.
Br,
Morten
CREATE TABLE cars (
id integer,
make varchar(32)
);
CREATE TABLE parts (
id integer,
car_id integer,
value varchar(64)
);
INSERT INTO cars (id, make) VALUES (1, 'Ford');
INSERT INTO cars (id, make) VALUES (2, 'Honda');
INSERT INTO parts (id, car_id, value) VALUES (1, 1, 'Wheel');
INSERT INTO parts (id, car_id, value) VALUES (2, 1, 'Tire');
INSERT INTO parts (id, car_id, value) VALUES (3, 2, 'Wheel');
INSERT INTO parts (id, car_id, value) VALUES (4, 2, 'Tire');
INSERT INTO parts (id, car_id, value) VALUES (5, 2, 'Dice');
SELECT cars.id,
(SELECT parts.value FROM parts WHERE car_id = 2 AND id = 3) AS
part_3,
(SELECT parts.value FROM parts WHERE car_id = 2 AND id = 4) AS
part_4
FROM cars
WHERE id = 2;
SELECT cars.id, parts_3.value AS part_3, parts_4.value AS part_4
FROM cars
INNER JOIN parts AS parts_3 ON cars.id = parts_3.car_id
INNER JOIN parts AS parts_4 ON cars.id = parts_4.car_id
WHERE parts_3.id = 3
AND parts_4.id = 4
AND cars.id = 2;
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
