Re: Curious SQL question

From: Tonkuma <tonkuma_at_jp.ibm.com>
Date: 6 Jan 2007 09:26:57 -0800
Message-ID: <1168104417.461627.154430@v33g2000cwv.googlegroups.com>

Tonkuma wrote:
> Or
> ------------------------- Commands Entered -------------------------
> SELECT Customer_ID, Product_ID
> FROM (SELECT C.Customer_ID, P.Product_ID
> FROM (SELECT DISTINCT
> Customer_ID
> FROM GutoSupplied.Orders) C
> INNER JOIN
> GutoSupplied.Products P
> ON 0=0
> ) CP
> EXCEPT
> SELECT O.Customer_ID, P.Product_ID
> FROM GutoSupplied.Orders O
> INNER JOIN
> GutoSupplied.Products P
> ON O.Product_ID = P.Product_ID
> ORDER BY
> Customer_ID, Product_ID;
> ------------------------------------------------------------------------------

>

> CUSTOMER_ID PRODUCT_ID
> ----------- -----------
> 1 5
> 2 3
> 2 4
> 2 5
> 3 2
> 3 5
> 4 1
> 4 2
> 4 3
> 4 4

>
> 10 record(s) selected.

Remove redundncy of my second example.

------------------------- Commands Entered -------------------------

(
 (SELECT C.Customer_ID, P.Product_ID

    FROM (SELECT DISTINCT

                 Customer_ID
            FROM GutoSupplied.Orders) C
         INNER JOIN
         GutoSupplied.Products P
            ON 0=0

  )
  EXCEPT
  SELECT Customer_ID, Product_ID
    FROM GutoSupplied.Orders
)
 ORDER BY
       Customer_ID, Product_ID;

---

CUSTOMER_ID PRODUCT_ID
----------- -----------

          1           5
          2           3
          2           4
          2           5
          3           2
          3           5
          4           1
          4           2
          4           3
          4           4

  10 record(s) selected. Received on Sat Jan 06 2007 - 11:26:57 CST