CODEWITHSUNDEEP
sqlpostgresql
Paul
Paul

Reputation: 93

How do I create a view by joining two tables in PostgreSQL?

I am a PostgreSQL newbie, and I have two tables like this:

  1. Attendees(AttendeeId, Name)
  2. Couples(CoupleId, AttendeeIdMan, AttendeeIdLady)

How do I create a view like this?

Attendees_Couple(CoupleId, Name_Man, Name_Lady)

For now, I came out with something like the below, but then I got stuck.

CREATE VIEW Attendees_Couple AS
SELECT a."Name"
FROM "Attendees" a, "Couples" c
WHERE a."AttendeeID" = c."AttendeeIdMan"....

Any hint or help will be appreciated!

Upvotes: 1

Views: 3217

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246248

You'll have to join with Attendees twice:

CREATE VIEW "Attendees_Couple" AS
SELECT c."CoupleId",
       a1."Name" AS "Name_Man",
       a2."Name" AS "Name_Lady"
FROM "Couples" AS c
   JOIN "Attendees" AS a1 ON c."AttendeeIdMan"  = a1."AttendeeId"
   JOIN "Attendees" AS a2 ON c."AttendeeIdLady" = a2."AttendeeId";

Do yourself a favor and use lower case names!

Upvotes: 5

Related Questions