Thanks codeWarrior - you got me 99% there - I just needed to add the NULL "trick" on the join w/ the contact_phone and contact_address tables and that got me the results I was after!
This is what I the final qry looks like : SELECT A.account_id, A.account_username, V.vendor_contract_signed_date, CE.contact_email, CN.contact_name_first, CA.contact_address_1, CP.contact_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.vendor_id = A.account_id) JOIN contact_email CE ON (CE.account_id = A.account_id OR CE.account_idIS 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 AND CN.domain_type_id = DT.domain_type_id AND CP.domain_type_id = DT.domain_type_id OR CN.domain_type_id IS NULL OR CP.domain_type_id IS NULL ) JOIN account_type AT ON (AT.account_type_id = A.account_type_id) HAVING AT.account_type_tag = 'ACCOUNT_VENDOR' AND DT.domain_type_tag = 'VENDOR_PRIMARY' Thanks for the help! =Rob On 1/25/07, codeWarrior <[EMAIL PROTECTED]> wrote:
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"" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] 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 [EMAIL PROTECTED] 555-1212 BILL123 ACTIVE Bill NULL [EMAIL PROTECTED] 456-4444 Steve1234 INACTIVE Steve 654 Hill St [EMAIL PROTECTED] 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 ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
