Skip to content
Advertisement

Join one table with two other ones by id

I am trying to join one table with two others that are unrelated to each other but are linked to the first one by an id

I have the following tables

Then I inserted the following data

Obs:

  • The admin does not has an invite
  • The group has an approved invitation with status 2 (because the member ‘other’ joined)
  • The group has two pending invites with status 1

I am trying to do a query that gets the following result

I have tried the following querys with no luck

Any ideas of what I am doing wrong?

Advertisement

Answer

Because members and invites are not related, you need to use two separate queries and use UNION (automatically removes duplicates) or UNION ALL (keeps duplicates) to get the output you desire:

Output:

Without a UNION, your query implies that the tables have some sort of relationship, so the columns are joined side-by-side. Since you want to preserve the null values, implying that the tables are not related, you need to concatenate/join them vertically with UNION

Disclosure: I work for EnterpriseDB (EDB)

User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement