CODEWITHSUNDEEP
sqlpostgresql
Mad Scientist
Mad Scientist

Reputation: 18553

Merge a one-to-many and a many-to-many relation when joining tables

I have a table of things and a table things_persons in PostgreSQL 9.6 that represents a many-to-many relationship between things and persons. But each thing also has a column owner, representing a one-to-many relationship.

A minimal schema for my problem is the following:

CREATE TABLE things(
  thing_id SERIAL,
  owner integer
 );

 CREATE TABLE things_persons(
   thing_id integer,
   person_id integer
);

INSERT INTO things VALUES(1,10);
INSERT INTO things VALUES(2,10);
INSERT INTO things_persons VALUES(1,10);
INSERT INTO things_persons VALUES(1,11);
INSERT INTO things_persons VALUES(2,11);

The following query is a part of what I want to do:

SELECT * FROM things
LEFT JOIN things_persons USING(thing_id)

The result is:

| thing_id | owner | person_id |
|----------|-------|-----------|
|        1 |    10 |        10 |
|        1 |    10 |        11 |
|        2 |    10 |        11 |

What I actually want to do is to treat the owner as if it was just another entry in the things_persons table. I want to list each person that is associated with each thing, regardless if they are an owner or in the things_persons table.

The following represents the result I want to achieve:

| thing_id | person_id |
|----------|-----------|
|        1 |        10 |
|        1 |        11 |
|        2 |        10 |
|        2 |        11 |

For thing 1, the owner is also among the person_ids, so it should not be duplicated. For thing 2, the owner is not among the person_ids, so it should be added there.

Changing the schema is not an option here, but I can't think of a way to write a query that gives me my desired result. Any idea on how to write such a query?

Upvotes: 3

Views: 206

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

I think you just want a union or union all:

SELECT tp.thing_id, tp.person_id
FROM things_persons tp 
UNION ALL
SELECT t.thing_id, t.owner_id
FROM things t;

You would use union if you wanted the query to remove duplicates.

Upvotes: 2

Related Questions