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

Question

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

Attendees(AttendeeId, Name)
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!

Laurenz Albe · Accepted Answer · 2018-11-28 21:21:12Z

6

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!

answered Nov 28, 2018 at 21:21

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

The Impaler Over a year ago

Oh yes, +1 for the use of lower case names.

Paul Over a year ago

That makes sense! Thanks a lot! So sad I am not the one who created those tables...

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related