-1

I have multiple tables in my Postgres database that are linked by a field called id. My main table, Person is linked to other tables Address, Phone andEmail by id.

This line of code gets information about the person from all tables in the database:

SELECT *
FROM "Person" p, "Address" a
WHERE p.id = a.id

This isn't showing rows where p.id exists, but we don't have an address for that specific person yet (a.id != p.id in any case).

How do I get the select statement to also return fields where a.id is not found to contain p.id?

philipxy
  • 14,867
  • 6
  • 39
  • 83
George Shalhoub
  • 1
  • 1
  • It's a strange naming convention that the foreign key column in `"Address"` that references the `"Person"` table is named `id` as well. What is the primary key column named in `"Address"`? –  Nov 21 '22 at 18:29
  • I said it like that so it is more simple. The Address file actually has an id column named "id", and the linked record is "person" – George Shalhoub Nov 21 '22 at 19:50
  • Does this answer your question? [How to select from two tables in MySQL even if not all rows in one table have corespondents in the other?](https://stackoverflow.com/questions/3969452/how-to-select-from-two-tables-in-mysql-even-if-not-all-rows-in-one-table-have-co) – philipxy Nov 22 '22 at 09:45

1 Answers1

0

You want an outer join which can only be done by getting rid of the ancient, outdated and fragile implicit joins in the WHERE clause and use a proper JOIN operator:

select *
from "Person" p
  left join "Address" a on p.id = a.id;
  • Great. And if I wanted to mimic this for more than just one table, would it look like: left join "Address" a on p.id = a.id, "Phone" ph on p.id = ph.id – George Shalhoub Nov 21 '22 at 19:49
  • "*can only be done by […] a proper JOIN operator*" - couldn't lateral subqueries also achieve this? – Bergi Nov 21 '22 at 20:37