Postgres Pro
Downloads
Home   >   mailing lists

Thread: Outer join question?

Outer join question?

From
Carol Walter
Date:
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.

Re: Outer join question?

From
Aurimas Černius
Date:
Hi,

> 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.


I think both joins should be LEFT.


--
Aurimas

Re: Outer join question?

From
Carol Walter
Date:
You're right.  This did it.

Thanks,
Carol

On Apr 8, 2009, at 9:12 AM, Aurimas Černius wrote:

> Hi,
>
>> 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.
>
>
> I think both joins should be LEFT.
>
>
> --
> Aurimas
>
> --
> Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice