mrik974 mrik974 - 5 months ago 8
SQL Question

SQL SELECT multiple keys/values

I've got a table

PERSON_PROPERTIES
that resembles the following :

| ID | KEY | VALUE | PERSON_ID |
| 1 | fname | robert | 1 |
| 2 | lname | redford | 1 |
| 3 | fname | robert | 2 |
| 4 | lname | de niro | 2 |
| 5 | fname | shawn | 3 |
| 6 | nname | redford | 3 |


I would like to
SELECT
(in JPQL or in PSQL) the
PERSON_ID
that matches the given
fname
and
lname
.

I've tried

`SELECT DISTINCT *
FROM PERSON_PROPERTIES t0
WHERE ((((t0.key = 'fname')
AND (t0.value = 'robert'))
AND ((t0.key = 'lname')
AND (t0.value = 'redford'))))`


but it returns me no value.

I've also tried

`SELECT DISTINCT *
FROM PERSON_PROPERTIES t0
WHERE ((((t0.key = 'fname')
AND (t0.value = 'robert'))
OR ((t0.key = 'lname')
AND (t0.value = 'redford'))))`


but this way it returns me all values. I don't know how to turn the query properly for it to give me only value
1
.

Answer
SELECT PERSON_ID
FROM PERSON_PROPERTIES
group by PERSON_ID
having sum(case when key = 'fname' and value = 'robert' then 1 else 0 end) > 0
and sum(case when key = 'lname' and value = 'redford' then 1 else 0 end) > 0

Groupy by the person and select only those having both values.

Comments