left join problem

shylock · #1 (**permalink**) 01-25-05, 07:02

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

r937 · #2 (**permalink**) 01-25-05, 07:20

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

r937 · #3 (**permalink**) 01-25-05, 07:22

please don't cross-post

see http://www.dbforums.com/t1097989.html

Internet Services

Web Development

Digital Marketing

Consumer Tech