JOIN on two tables

user_4598413 0
Viewed 30

I have two tables:

user:

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?

join postgresql sql
1 Answers

You can use two joins:

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';
user_11440350
Related