CODEWITHSUNDEEP
phpmysql
Henk
Henk

Reputation: 201

How to query based on values based on multiple rows in other table associated with row

For example i have these tables:

A:

id | something etc.
---|--------------
1  | bla
2  | die
3  | bla

B:

a_id | key | value
-----|-----|
1    | x   | a
1    | y   | b
1    | z   | c
2    | x   | a
2    | z   | d
3    | x   | a
3    | z   | c

How do i make a query where i get all rows from table A that have an id that is linked in table b and has key x and value a and key z and value c which should result in row 1 and 3 in this example.

i really can't wrap my head around it, any help would be greatly appreciated.

Upvotes: 1

Views: 34

Answers (1)

juergen d
juergen d

Reputation: 204854

select a.*
from a
join 
(
   select a_id
   from b
   group by a_id
   having sum(key = 'x' and value = 'a') > 0
      and sum(key = 'z' and value = 'c') > 0
) b_tmp on b_tmp.a_id = a.id

The subquery gets all relevant records from table b. Join againt that and you have your result.

Upvotes: 2

Related Questions