CODEWITHSUNDEEP
sql
RV.
RV.

Reputation: 2998

How to query multiple linked record to return one row in SQL

I have two tables like this:

Product table                        Identity table


Product | Role  | id                  id | Flag
--------+-------+----                 ---+-----
A       | Phone | 1                    1 |  Y
A       | iPad  | 2                    2 |  Y
A       | PC    | 3                    3 |  N

I want to query this table in such a way that I get following result (All products where flag is Y):

Product | Role1  | Role2 
--------+--------+-------------
A       | Phone  | iPad

Please suggest how this can be done.

Upvotes: 0

Views: 101

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

This will work for up to two roles:

select p.product,
       min(role) as role_1,
       (case when max(role) <> min(role) then max(role) end) as role_2
from product p join
     identity i
     on p.Id = i.Id and i.flag = 'Y'
group by p.product;

Upvotes: 1

finidigeorge
finidigeorge

Reputation: 149

Like this:

SELECT Product,
 MAX(CASE WHEN Role='Phone' THEN value END) as Role1,
 MAX(CASE WHEN Role='IPad' THEN value END) as Role2,
 MAX(CASE WHEN Role='PC' THEN value END) as Role3
FROM Product
JOIN Identity ON Product.Id = Identity.Id AND Identity.Flag = 'Y'
GROUP BY Product

You can add as many roles as you need to the select list

Upvotes: 1

Related Questions