Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-novice by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group