Tonkuma wrote:
> Or
> ------------------------ Commands Entered ------------------------
> SELECT Customer_ID, Product_Name
> FROM (SELECT C.Customer_ID, Product_Name
> FROM (SELECT DISTINCT
> Customer_ID
> FROM Guto.Orders) C
> INNER JOIN
> Guto.Products
> ON 0=0
> ) CP
> EXCEPT
> SELECT O.Customer_ID, Product_Name
> FROM Guto.Orders O
> INNER JOIN
> Guto.Products P
> ON O.Product_ID = P.Product_ID
> ORDER BY
> Customer_ID, Product_Name;
> ------------------------------------------------------------------------------
>
> CUSTOMER_ID PRODUCT_NAME
> ----------- ------------
> john bread
> john wine
> mary shampoo
> mary wine
>
> 4 record(s) selected.
Second sample in my previous post may misleading the meaning of query
because of inappropriate indention.
Here is corrected one.
INNER JOIN
Guto.Products
ON 0=0
These lines moved to left.
------------------------ Commands Entered ------------------------
SELECT Customer_ID, Product_Name
FROM (SELECT C.Customer_ID, Product_Name
FROM (SELECT DISTINCT
Customer_ID
FROM Guto.Orders) C
INNER JOIN
Guto.Products
ON 0=0
) CP
EXCEPT
SELECT O.Customer_ID, Product_Name
FROM Guto.Orders O
INNER JOIN
Guto.Products P
ON O.Product_ID = P.Product_ID
ORDER BY
Customer_ID, Product_Name;
Depending on your DBMS, following may result better performance.
SELECT Customer_ID, Product_Name
FROM (
(SELECT C.Customer_ID, P.Product_ID
FROM (SELECT DISTINCT
Customer_ID
FROM Guto.Orders) C
INNER JOIN
Guto.Products P
ON 0=0
)
EXCEPT
SELECT Customer_ID, Product_ID
FROM Guto.Orders
) NP
INNER JOIN
Guto.Products P
ON P.Product_ID = NP.Product_ID
ORDER BY
Customer_ID, Product_Name;
Received on Thu Jan 04 2007 - 14:04:53 CST
