CODEWITHSUNDEEP
sqldatabasepostgresql
Mehmet Ali
Mehmet Ali

Reputation: 363

Selecting two tables at the same time

I have users and user_groups tables like following:

users

id - PK
name
timestamp

user_groups

user_1 - Foreign key to users id
user_2 - Foreign key to users id
user_3 - Foreign key to users id
type

I have a user having id = 1. I want to select all the groups that this user joint, the name of the group members and the type of the user group. Can I do that with only one query? What would be the best way to achieve this?

Sample Data

users

id - 1
name - Jeff

id - 2
name - Mehmet

id - 3
name - Walter

user_ groups

user_1 - 1
user_2 - 2
user_3 - null
type - 1

user_1 - 1
user_2 - 2
user_3 - 3
type - 1

user_1 - 1
user_2 - 3
user_3 - null
type - 2

How output should be:

[{
type: 1,
name: ["Jeff", "Mehmet"]
},

{
type: 1,
name: ["Jeff","Mehmet","Walter"]
},

{
type: 2,
name: ["Jeff", "Mehmet"]
}]

Upvotes: 0

Views: 37

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270633

You want multiple joins and some array functionality:

select ug.type,
       array_remove(array[u1.name, u2.name, u3.name], null)
from user_groups ug left join
     users u1
     on ug.user_1 = u1.id left join
     users u2
     on ug.user_2 = u2.id left join
     users u3
     on ug.user_3 = u3.id;

Upvotes: 1

Related Questions