CODEWITHSUNDEEP
sqldb2
headacheFromCoding
headacheFromCoding

Reputation: 15

Find the sids of suppliers who supply a red part AND a green part

i have a question about some sql.

Thank you very much for your Help.

I have tried to Join the two tables, but i dont know how to get the output needed.

Select SID, COLOR from cat c1 JOIN par p ON c1.PID = p.PID;

SID  COLOR     
---  ---------

S1  red       
S1  silver    
S1  red       
S2  red       
S2  red       
S2  red       
S3  red       
S3  green     
S4  trans     
S4  cyan      
S4  magenta

This is the output i need:

SID    COLOR1       COLOR2     
-----  ----------  ---------- 
S3     green        red

This are the provided Tables:

SELECT * FROM parts 



PID    PNAME      COLOR        WEIGHT CITY       
-----  ----------  ----------  ------ ----------

P1    Nut        red            13 London     
P2    Bolt       black          18 Paris      
P3    Screw      red            17 Rome       
P4    Screw      silver         14 London     
P5    Cam        trans          12 Paris      
P6    Cog        cyan           19 London     
P7    Nut        magenta        15 -          
P8    Wheel      red            15 Munich     
P9    Bearing    green          15 Milano     

  9 record(s) selected. 




SELECT * FROM catalog 

SID   PID   COST         
-----  -----  ------------ 

S1    P3            0.50 
S1    P4            0.50 
S1    P8           11.70 
S2    P1           16.50 
S2    P3            0.55 
S2    P8            7.95 
S3    P8           12.50 
S3    P9            1.00 
S4    P5            2.20 
S4    P6      1247548.23 
S4    P7      1247548.23 

  11 record(s) selected.

Upvotes: 0

Views: 2442

Answers (1)

forpas
forpas

Reputation: 164174

You must group by cat.sid and put the condition in the HAVING clause:

select 
  c.sid, 
  min(p.color) color1 ,
  max(p.color) color2
from cat c inner join par p 
on c.pid = p.pid
where p.color in ('green', 'red')
group by c.sid
having count(distinct p.color) = 2

After selecting only parts with the 2 colors, there will be only suppliers who supply 1 or both colors.
The condition having count(distinct p.color) = 2 returns only the suppliers who supply both colors.
min() and max() return the 2 colors. They are not really needed in this case but I preferred to use them instead of hardcoding them.

Upvotes: 1

Related Questions