Two tables :
customer -> customerid
address -> customerid,addresstype
a customer can have more types of addresses :
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 ?
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 (+)
a1.addresstype = 'postaddress'
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"