Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 647EB9FB63A for ; Thu, 25 Jan 2007 19:51:40 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-new, port 10024) with ESMTP id 58502-03 for ; Thu, 25 Jan 2007 19:51:32 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.175]) by postgresql.org (Postfix) with ESMTP id 2855D9FB4C8 for ; Thu, 25 Jan 2007 19:51:35 -0400 (AST) Received: by ug-out-1314.google.com with SMTP id k3so571870ugf for ; Thu, 25 Jan 2007 15:51:34 -0800 (PST) DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:mime-version:content-type; b=Ovfsj7pMW05TLAMuw5Tad7fJ4v2u0DXu4twCOynqLZQL5HpQMReb1gKjxxkKCHLtRQ6oD5JbhOIJGJthvMK88jmHPuANaXxEe6snH/Tt2oAfkfnDDe3Gy687+qoYojMfkrAe0ynRVG47ORaNCKk3kPcaTOGu7Aae/YWvzgi1cHc= Received: by 10.78.172.20 with SMTP id u20mr536103hue.1169769094350; Thu, 25 Jan 2007 15:51:34 -0800 (PST) Received: by 10.78.179.5 with HTTP; Thu, 25 Jan 2007 15:51:34 -0800 (PST) Message-ID: <34785f060701251551u23538daej2b45f0c522bf1b7a@mail.gmail.com> Date: Thu, 25 Jan 2007 18:51:34 -0500 From: "Rob V" To: pgsql-sql@postgresql.org Subject: LEFT Join Question MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_24415_23939249.1169769094237" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200701/126 X-Sequence-Number: 27278 ------=_Part_24415_23939249.1169769094237 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline 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 ------=_Part_24415_23939249.1169769094237 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline
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
------=_Part_24415_23939249.1169769094237--