Re: Simple How To: SQL left Join with and clause

joes wrote:
> Hello there
>
> I try to rewrite an Access queries for Oracle 8. Now I get in touch
> with 'LEFT JOIN' and need there some little hint how to do that.
>
> simple SQL problem:
> clients, which some of them haves orders. Now I would like to have all
> clients which have either no orders or orders which have a total price
> less than 10$
> (something like this, is not so important...)
>
> SELECT * FROM client LEFT JOIN order ON (client.id = order.clientid
> and order.price < 10.0)
>
> I have an and clause in my LEFT JOIN. How would this be look like in
> oracle ?
>
> SELECT * FROM client, order WEHER client.id = order.clientid(+)
> and order.price < 10.0
>
> is not working because in this case I loose clients which have no
> orders
> so any ideas how to realize that ?
>
> thank you for your time.
>
> regards
> Mark

You don't need to rewrite that much. Left join are ANSI standard, so this sould be valid.
SELECT *
FROM client LEFT JOIN order

   ON (client.id = order.clientid)
WHERE order.price < 10.0;

Note the addition of a where clause....
I'm not sure if that works on 8i, it works on 9i....

An idea about why you dont get the desired effect of the outer join: The resulting rows that don't have any order data, do not have any order price either... This means that you check if NULL is less than 10. Oracle won't return such rows...

In this SQL i'm assuming that order.clientid is a NOT NULL column. Try rewriting your code to this:

SELECT *
FROM client, order
WHERE client.id = order.clientid(+)

   AND (order.price < 10.0 OR order.clientid IS NULL);

Or the ANSI equivalent:
SELECT *
FROM client LEFT JOIN order

   ON (client.id = order.clientid)
WHERE order.price < 10.0 OR order.clientid IS NULL;

regards
Andreas Received on Tue Feb 24 2004 - 10:01:36 CST