Results 1 to 4 of 4
  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
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    select * from customers,address where
    customer.customerid = address.customerid (+) and
    address.addresstype (+) = 'postaddress'

  3. #3
    Join Date
    Jun 2003
    Posts
    7

    outer join

    Hello, this indeed works but I've forgot to tell that I can't use this construction because it seems to be very slow with the very large tables I am using. I am looking for a solution that does the same but without the '(+)' = construction.

    I almost have something like this :

    select c.* from customer c,address a1 where c.relationid = a1.relationid (+)
    and
    (
    a1.addresstype = 'postaddress'
    or (
    not exists
    (
    select 1 from address a2 where a2.addressid = a1.addressid and
    (a2.addresstype <> 'postaddress')
    )

    )
    )

    but it is not returning customers that have only one address with type "privateaddress"

    Grt Christian

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    The conclusion is not that you can't use this (correct) construction, but that there is a tuning issue. What does EXPLAIN PLAN show?

Posting Permissions

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