CODEWITHSUNDEEP
mysqlsqlperformanceoptimization
aadarshsg
aadarshsg

Reputation: 2089

Query Optimisation - Collating result from one to many relationship table efficiently in SQL

I am trying to combine one-to-many table. I want to display the variants for one item together. This has to be done entirely in SQL.

Here is the sample Schema

CREATE TABLE ItemVariant (`item_id` int, `variant_id` int, `variant` varchar(55), `variant_order` int);
INSERT INTO ItemVariant (`variant_id`, `item_id`, `variant`, `variant_order`)
VALUES
    (1, 1, 'I1V1', 1),
    (2, 1, 'I1V2', 2),
    (3, 1, 'I1V3', 3),
    (4, 2, 'I2V1', 1)
;

CREATE TABLE Item (`item_id` int, `item` varchar(55));
INSERT INTO Item (`item_id`, `item`)
VALUES (1,'I1'), (2,'I2');

Here is a working query which gives me what is required. SQLFiddle

SELECT Item.item, iv1.variant, iv2.variant, iv3.variant from Item
LEFT JOIN ItemVariant iv1 ON (Item.item_id = iv1.item_id and iv1.variant_order=1)
LEFT JOIN ItemVariant iv2 ON (Item.item_id = iv2.item_id and iv2.variant_order=2)
LEFT JOIN ItemVariant iv3 ON (Item.item_id = iv3.item_id and iv3.variant_order=3)

However, as you can see, I have to access ItemVariant table 3 times and hence, it is not efficient. I thought of using group by but I am unable to query inside the groups formed by group by.

Here is what I tried for group by. SQLFiddle

SELECT 
*, 
case when variant_order=1 then variant end as variant1,
case when variant_order=2 then variant end as variant2,
case when variant_order=3 then variant end as variant3
from  ItemVariant
GROUP BY item_id

Upvotes: 2

Views: 49

Answers (2)

Rick James
Rick James

Reputation: 142366

SELECT  i.item,
        GROUP_CONCAT(v.variant
                     ORDER BY  v.variant_order
                     SEPARATOR ', ' ) AS Variants
    FROM  Item AS i
    JOIN  ItemVariant as v  ON v.item_id = i.item_id
    GROUP BY  i.item;

Upvotes: 0

sqluser
sqluser

Reputation: 5672

Try this:

SELECT Item.item, 
       MAX(case when variant_order=1 then variant end) as variant1,
       MAX(case when variant_order=2 then variant end) as variant2,
       MAX(case when variant_order=3 then variant end) as variant3
from ItemVariant
LEFT JOIN Item ON Item.item_id = ItemVariant.item_id
GROUP BY Item.item;

Upvotes: 2

Related Questions