ANSI syntax for outer join
In the section titled "Outer Join for a Simple Join to a Third Table",
the IDS Tutorial mentions the following example:
SELECT c.customer_num, c.lname, o.order_num, i.stock_num, i.manu_code,i.quantity
FROM customer c, LEFT OUTER JOIN (orders o, items i)
WHERE c.customer_num = o.customer_num
AND o.order_num = i.order_num
AND manu_code IN ('KAR', 'SHM')
ORDER BY lname;
http://publib.boulder.ibm.com/infocenter/idshelp/v111/index.jsp?topic=/com.ibm.sqlt.doc/sqlt96.htm
This example fails with syntax errors. The following, using the
Informix extension works fine:
SELECT c.customer_num, c.lname, o.order_num, i.stock_num, i.manu_code,i.quantity
FROM customer c, OUTER (orders o, items i)
WHERE c.customer_num = o.customer_num
AND o.order_num = i.order_num
AND manu_code IN ('KAR', 'SHM')
ORDER BY lname;
Is it possible have a query that uses ANSI syntax to have a simple
join on two tables which is outer join-ed to a third table?
Thanks in advance.
Krishna wrote:
> In the section titled "Outer Join for a Simple Join to a Third Table",
> the IDS Tutorial mentions the following example:
>
> SELECT c.customer_num, c.lname, o.order_num, i.stock_num, i.manu_code,> i.quantity
> FROM customer c, LEFT OUTER JOIN (orders o, items i)
> WHERE c.customer_num = o.customer_num
> AND o.order_num = i.order_num
> AND manu_code IN ('KAR', 'SHM')
> ORDER BY lname;
>
> http://publib.boulder.ibm.com/infocenter/idshelp/v111/index.jsp?topic=/com.ibm.sqlt.doc/sqlt96.htm
>
> This example fails with syntax errors. The following, using the
> Informix extension works fine:
>
> SELECT c.customer_num, c.lname, o.order_num, i.stock_num, i.manu_code,> i.quantity
> FROM customer c, OUTER (orders o, items i)
> WHERE c.customer_num = o.customer_num
> AND o.order_num = i.order_num
> AND manu_code IN ('KAR', 'SHM')
> ORDER BY lname;
>
> Is it possible have a query that uses ANSI syntax to have a simple
> join on two tables which is outer join-ed to a third table?
>
> Thanks in advance.
Well... I'm old school... I can't write an ANSI SQL query... But I did give it
a try:
SELECT c.customer_num, c.lname, o.order_num, i.stock_num, i.manu_code,i.quantity
FROM customer c LEFT outer JOIN (orders o JOIN items i on (o.order_num=
i.order_num) )
on (c.customer_num = o.customer_num)
WHERE i.manu_code IN ('KAR', 'SHM')
ORDER BY lname;
This apparently doesn't work (looks like a normal JOIN), but then I did this:
SELECT c.customer_num, c.lname, o.order_num, i.stock_num, i.manu_code,i.quantity
FROM customer c LEFT outer JOIN (orders o JOIN items i on (o.order_num=
i.order_num) )
on (c.customer_num = o.customer_num)
WHERE i.manu_code is NULL or i.manu_code IN ('KAR', 'SHM')
ORDER BY lname;
And apparently it works. I couldn't write a WHERE clause inside the INNER JOIN,
so I had to include a condition to allow the "outer" rows...
Maybe this is enough to get you going?
The statement on the docs has several errors...
Regards.