Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2003
    Posts
    7

    Question Unanswered: left join problem

    Hello,
    Two tables :
    customer -> customerid
    address -> customerid,addresstype


    a customer can have more types of addresses :
    postaddress,privateaddress

    I want to show all customers with their addresses.
    They addresses must be of type 'postaddress'
    If a customer doesn't have a address of type 'postaddress' then still show the record with the customerfields and empty address fields.

    query : select * from customers,address where
    customer.customerid = address.customerid (+) and
    addresstype = 'postaddress'

    This query shows only the customers with "postaddresses" but not the customers without a address or customers with a "privateaddress".

    So I am looking for a query that shows the customers with postaddresses and also the customers with empty or other type of addresses but then they addressfields should be empty.
    Who can help me ?
    Thanx Christian

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i don't understand that weird (+) syntax, so i hope you understand standard sql
    Code:
    select c.customerid
         , c.customername 
         , a.address
      from customers as c
    left outer
      join address as a
        on c.customerid = a.customerid 
       and a.addresstype = 'postaddress'
    in the above query, notice that the addresstype condition is in the ON clause

    this means it is a condition of the join

    let's imagine a customer without a postaddress

    since this is a condition of the join, that customer will be returned with NULL in the address

    by comparison, consider what happens when the addresstype condition is in the WHERE clause --
    Code:
    select c.customerid
         , c.customername 
         , a.address
      from customers as c
    left outer
      join address as a
        on c.customerid = a.customerid 
     WHERE a.addresstype = 'postaddress'
    now suppose there is a customer with no address at all

    in a LEFT OUTER join, this customer is also returned with NULL in the address

    but when the WHERE clause is evaluated, NULL is not equal to 'postaddress' so that customer is filtered out!

    this in effect reverts the results of the join to the same as if you had specified an INNER join, and you get only customers with a postaddress
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    please don't cross-post

    see http://www.dbforums.com/t1097989.html
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •