I have two tables:
user
:
x
id username name
1aa sample.user Sample User
and credential
:
id userId type value
1 1aa salt abc123
2 1aa password samplehashpassword
I want to perform a query to get the following result (one row for every user):
userId username name type value type value
1aa sample.user Sample User salt abc123 password samplehashpassword
Right now I am executing this query:
SELECT
"userId", username, name, type, value
FROM
public.user u
INNER JOIN public.credential c
ON u.id = c."userId";
This results in:
userId username name type value
1aa sample.user Sample User salt abc123
userId username name type value
1aa sample.user Sample User password samplehashpassword
I want a single row for a user. How can I achieve this?
Advertisement
Answer
You can use two join
s:
SELECT u.*, cs.type, cs.value, cp.type, cp.value
FROM public.user u INNER JOIN
public.credential cs
ON u.id = cs."userId" AND cs.type = 'salt' INNER JOIN
public.credential cp
ON u.id = cp."userId" AND cp.type = 'password';