Re: LEFT Join Question

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 <gpatnude(at)hotmail(dot)com> 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"" <taketwosolutions(at)gmail(dot)com> wrote in message
> news:34785f060701251551u23538daej2b45f0c522bf1b7a(at)mail(dot)gmail(dot)com(dot)(dot)(dot)
> 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(at)here(dot)com 555-1212
> BILL123 ACTIVE Bill
> NULL bill(at)here(dot)com 456-4444
> Steve1234 INACTIVE Steve 654
> Hill St steve(at)here(dot)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
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>