J13t0u J13t0u - 17 days ago 7
MySQL Question

MySQL query find matching column based on another column?

Sorry I don't know how to word the question's title.
I have a table like this

Prod Part Number

0001 101 3
0001 102 2
0001 103 1

0002 101 3
0002 102 2
0002 103 4

0003 101 2
0003 102 3
0003 103 6

0004 101 3
0004 102 2
0004 103 1


I want to find the product that has the correct number per part for all parts.
So something like

SELECT Prod from table
WHERE (Number of Part(101)) = 3
AND (Number of Part(102)) = 2
AND (Number of Part(103)) = 1


Output would be:

Prod
0001
0004


How can I achieve that?

Answer

You could use some inner join

  select t1.Prod from 
  ( select Prod 
  from my_table 
  where part = 101
  and Number = 3 )  t1
  inner join  
  ( select Prod 
  from my_table 
  where part = 102
  and Number = 2 )  t2 on t1.Prod = t2.Prod
  inner  join 
  ( select Prod 
  from my_table 
  where part = 103
  and Number = 1 ) t3 on t1.Prod = t3.Prod

http://sqlfiddle.com/#!9/014fe4/8