Re: LEFT Join Question - Mailing list pgsql-sql

Fisrt -- you probably want to start by doing fully qualified JOINS and then 
you want to allow joins with nulls on the columns that are allowed to be 
empty: I am doing this sort of off the top of my head ... but the thing you 
need to do generally is to COMPLETELY QUALIFY all of your joins and then use 
the "OR field IS NULL" trick. That should solve your problem.


SELECT A.account_id, A.account_username, V.vendor_status,CN.name, 
CA.address,CE.email, CP.phone
FROM account A
LEFT JOIN contact_phone CP ON (CP.account_id = A.account_id OR CP.account_id 
IS NULL)
LEFT JOIN contact_address CA ON (CA.account_id = A.account_id OR 
CA.account_id IS NULL),
JOIN vendor V ON (V.account_id = A.account_id),
JOIN contact_email CE ON (CE.account_id = A.account_id OR CE.account_id IS 
NULL),
JOIN contact_name CN ON (CN.account_id = A.account_id),
JOIN domain_type DT ON (CE.domain_type_id = DT.domain_type_id ),
JOIN account_type AT ON (AT.account_type_id = A..account_type_id)

HAVING A.account_type_tag = 'ACCOUNT_VENDOR' AND DT.domain_type_tag = 
'VENDOR_PRIMARY'




""Rob V"" <taketwosolutions@gmail.com> wrote in message 
news:34785f060701251551u23538daej2b45f0c522bf1b7a@mail.gmail.com...
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?)

SELECTaccount.account_id,account.account_username,vendor.vendor_status,contact_name.name,contact_address.address,contact_email.email,contact_phone.phone
FROMaccount aLEFT 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,
 
WHEREvendor.vendor_id = account.account_id ANDcontact_email.account_id = account.account_id ANDcontact_name.account_id
=account.account_id ANDaccount.account_type_id = account_type.account_type_id ANDcontact_email.domain_type_id =
domain_type.domain_type_idANDcontact_name.domain_type_id = domain_type.domain_type_id ANDvendor.vendor_status_code_id =
vendor_status_code.vendor_status_code_idANDaccount_type.account_type_tag = 'ACCOUNT_VENDOR'
ANDdomain_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