Home > mailing lists

LEFT Join Question - Mailing list pgsql-sql

From	Rob V
Subject	LEFT Join Question
Date	January 25, 2007 22:51:40
Msg-id	34785f060701251551u23538daej2b45f0c522bf1b7a@mail.gmail.com Whole thread Raw
Responses	Re: LEFT Join Question ("Rob V" <taketwosolutions@gmail.com>) Re: LEFT Join Question (Andrew Sullivan <ajs@crankycanuck.ca>)
List	pgsql-sql

Tree view

Hello all,

Ive been racking my brain for a few hours now and need some help, please!!!

I have the following tables :

account
=account_id
=account_type_id
=account_username

vendor
=account_id
=vendor_status

contact_name
=account_id
=name
=domain_type_id

contact_address
=account_id
=address
=domain_type_id

contact_email
=account_id
=email
=domain_type_id

contact_phone
=account_id
=phone
=domain_type_id

account_type
=account_type_id
= account_type_tag
records :
1 VENDOR
2 SELLER
3 CONTRACTOR

domain_type
=domain_type_id
=domain_type_tag
records :
1 PRIMARY
2 SECONDARY

Im looking for a select that will give me all records where the account_type_tag ="VENDOR" and domain_type_tag = "PRIMARY"
even if the address, email and phone are blank.

Users WILL HAVE records in the following tables :
account,

vendor

contact_name

contact_email

they MAY or MAYNOT have records in the following tables :
contact_address

contact_phone

I know I have to use a left join - but I can seem to figure out the syntax when dealing w/ different columns of the same table.

Im basically looking for this :

account_username vendor_status name address email phone
---------------------------------------------------------------------------------------------------------------------------------------------------------

Rob123                       ACTIVE                  ROB             123 Test Drive    rob@here.com    555-1212
BILL123                       ACTIVE                  Bill                 NULL              bill@here.com     456-4444
Steve1234                  INACTIVE             Steve                 654 Hill St         steve@here.com NULL

I know I have to use a left join - but I can seem to figure out the syntax when dealing w/ different columns of the same table.
(do I still need the = in the where clause when using a left join?)
SELECT
account.account_id,
account.account_username,
vendor.vendor_status,
contact_name.name,
contact_address.address,
contact_email.email,
contact_phone.phone
FROM
account a
LEFT JOIN contact_phone on (contact_phone.account_id = a.account_id)
LEFT JOIN contact_address on (contact_address.account_id = a.account_id),
vendor,
contact_email,
contact_name,
domain_type,
account_type,
WHERE
vendor.vendor_id = account.account_id AND
contact_email.account_id = account.account_id AND
contact_name.account_id = account.account_id AND
account.account_type_id = account_type.account_type_id AND
contact_email.domain_type_id = domain_type.domain_type_id AND
contact_name.domain_type_id = domain_type.domain_type_id AND
vendor.vendor_status_code_id = vendor_status_code.vendor_status_code_id AND
account_type.account_type_tag = 'ACCOUNT_VENDOR' AND
domain_type.domain_type_tag = 'VENDOR_PRIMARY'

The problem Im having is b/c Im only looking for specific domain types I have a join on the contact address and phone tables where the

domain types match - but if there are no records - it causes the entire record not to be show - so I need to do a left join on that table as well

but If I try to do it - I get an error " table name "contact_phone" specified more than once"

Any help would be great!!!

Thanks
-Rob

pgsql-sql by date:

From: "Jie Liang"
Date: 25 January 2007, 19:58:27
Subject: Re: shared_buffers and shmall,shmmax

From: "Rob V"
Date: 25 January 2007, 23:09:59
Subject: Re: LEFT Join Question

LEFT Join Question - Mailing list pgsql-sql

Previous

Next