pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedLEFT Join Question
5+ messages / 3 participants
[nested] [flat]
* LEFT Join Question
@ 2007-01-25 23:51 Rob V <taketwosolutions@gmail.com>
0 siblings, 3 replies; 5+ messages in thread
From: Rob V @ 2007-01-25 23:51 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: LEFT Join Question
@ 2007-01-26 00:09 Rob V <taketwosolutions@gmail.com>
parent: Rob V <taketwosolutions@gmail.com>
2 siblings, 0 replies; 5+ messages in thread
From: Rob V @ 2007-01-26 00:09 UTC (permalink / raw)
To: pgsql-sql
Just 1 followup to this :
they MAY or MAYNOT have records in the following tables :
contact_address,contact_phone
There may also be multiple records in that table w/ the same account_id -
but the domain_type_id will be different.
so the contact_phone could have 2 records :
account_id 1, domain PRIMARY phone 555-1212
account_id 1, domain SECONDARY phone -123-4556
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: LEFT Join Question
@ 2007-01-26 02:13 codeWarrior <gpatnude@hotmail.com>
parent: Rob V <taketwosolutions@gmail.com>
2 siblings, 1 reply; 5+ messages in thread
From: codeWarrior @ 2007-01-26 02:13 UTC (permalink / raw)
To: 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?)
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: LEFT Join Question
@ 2007-01-26 12:58 Andrew Sullivan <ajs@crankycanuck.ca>
parent: Rob V <taketwosolutions@gmail.com>
2 siblings, 0 replies; 5+ messages in thread
From: Andrew Sullivan @ 2007-01-26 12:58 UTC (permalink / raw)
To: pgsql-sql
On Thu, Jan 25, 2007 at 06:51:34PM -0500, Rob V wrote:
>
> 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.
I haven't tested this to remind myself for sure that it will work,
but I think you ought to be able to RIGHT OUTER JOIN the table you
just LEFT JOINed to to the next table using a different column.
A
--
Andrew Sullivan | ajs@crankycanuck.ca
Information security isn't a technological problem. It's an economics
problem.
--Bruce Schneier
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: LEFT Join Question
@ 2007-01-26 14:44 Rob V <taketwosolutions@gmail.com>
parent: codeWarrior <gpatnude@hotmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Rob V @ 2007-01-26 14:44 UTC (permalink / raw)
To: codeWarrior <gpatnude@hotmail.com>; +Cc: pgsql-sql
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@hotmail.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@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?)
> 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
>
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2007-01-26 14:44 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-01-25 23:51 LEFT Join Question Rob V <taketwosolutions@gmail.com>
2007-01-26 00:09 ` Rob V <taketwosolutions@gmail.com>
2007-01-26 02:13 ` codeWarrior <gpatnude@hotmail.com>
2007-01-26 14:44 ` Rob V <taketwosolutions@gmail.com>
2007-01-26 12:58 ` Andrew Sullivan <ajs@crankycanuck.ca>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox