CODEWITHSUNDEEP
mysqlsql
user1869132
user1869132

Reputation: 75

join select from multiple row values?

Two tables

1) product

--------------------
id  |  Name   |  price
1   |   p1    |    10
2   |   p2    |    20
3   |   p3    |    30

2) product_attributes:

---------------------------------------------------
id | product_id | attribute_name | attribute_value
---------------------------------------------------
1  |   1        |  size          |     10
2  |   1        |  colour        |     red
3  |   2        |  size          |     20

I need to join these two tables. In the where clause I need to match both the two rows attribute values. Is it possible to get the result based on two rows value. Based on the user selection,I need to display all the products matching the condition. Here if size=10 and colour=red.

Output should be

1   |   p1    |    10

It could be greatly helpful to get a query for this.

Upvotes: 3

Views: 162

Answers (1)

Barmar
Barmar

Reputation: 780851

select p.* from product p
join (select a1.product_id id from product_attributes a1 join product_attributes a2 using (product_id)
      where a1.attribute_name = 'size' and a1.attribute_value = '10'
        and a2.attribute_name = 'colour' and a2.attribute_value = 'red') pa
using (id)

If you need to match more attributes, just add more joins to the subquery.

Upvotes: 2

Related Questions