Outer join question?

From: Carol Walter <walterc(at)indiana(dot)edu>
To: pgsql-novice(at)postgresql(dot)org
Subject: Outer join question?
Date: 2009-04-08 13:02:54
Message-ID: F38B56F6-706C-4B51-BFC6-5C7E8DC34D3A@indiana.edu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

Hello,

I have three tables that create a many-to-many relationship between
two of them. One of them has person data and one of them has contact
data, and, of course, the other is the bridge table. I want to select
all the people in the people table with the last name like Smith and
join it with their contact information. Sometimes they have no
contact information, but I want to put them on my list anyway. I
typically use the old syntax of the "where clause" to join tables, so
I have two questions. Is there a syntax in postgres to do an outer
join using the "where clause" and what would be the correct syntax to
join the three tables using the newer standard,

SELECT last_name, first_name, contact
FROM people
LEFT (or RIGHT) JOIN bridge ON bridge.peopleid = people.peopleid
JOIN contact ON bridge.contactid = contact.contactid
WHERE last_name like 'Smi%';?

The above query is incorrect and I can't seem to get the result I'm
looking for.

Thanks,
Carol

PS This is PostgreSQL 8.3.6, running on a Solaris 10 box.

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Aurimas Černius 2009-04-08 13:12:29 Re: Outer join question?
Previous Message P Kapat 2009-04-07 16:34:53 Re: drop a table from non-public schema