0

I have 3 tables, TableA, TableB and TableAB (many to many table).

See example of TableAB below:

TableAB

id_attribute | id_product_attribute
14 | 18
14 | 19
16 | 10
16 | 12
16 | 16
16 | 18
16 | 20
16 | 22
16 | 24
16 | 26

In this table, I have id_attribute from TableA and id_product_attribute from TableB.

If I want to find out the id_product_attribute that has both id_attributes 14 and 16, how should I do it?

Essentially, I have a combination of id_attributes and I am looking for a specific id_product_attribute that contains all of them.

In the example above, only product_attribute 18 has both attributes 14 and 16. My first query will find out all the attributes. This problem I have now is to determine which product attribute it is from this table.

Thanks.

naveen goyal
  • 4,571
  • 2
  • 16
  • 26
Khang Wei
  • 1
  • 2

2 Answers2

1

You want a self-join.

select distinct ab1.id_product_attribute
from   tableAB ab1
join   tableAB ab2 on ab1.id_product_attribute = ab2.id_product_attribute
where  ab1.id_attribute = 14
and    ab2.id_attribute = 16

This first creates a selection of tableAB containing only id_attribute 14. Then another selection containing only id 16. Then joining those two to find only the matches.

winkbrace
  • 2,682
  • 26
  • 19
0
select 
   count(ab1.id_product_attribute),ab1.id_product_attribute,ab1.id_attribute
from ab ab1
join ab ab2
on ab1.id_product_attribute = ab2.id_product_attribute
where ab1.id_attribute in (14,16) 
and ab2.id_attribute in (14,16)
group by ab1.id_product_attribute,ab1.id_attribute
having count(ab1.id_product_attribute) > 1;

SQL Fiddle: http://sqlfiddle.com/#!2/007eb/13

Kevin Bowersox
  • 93,289
  • 19
  • 159
  • 189