Re: sql inner join?

Maros Kollar wrote:

>Hi,
>
>i'm currently trying to assemble a relatively complex SQL query. (for oracle
>8). There are two tables involved in this query:
>
>Table 1.: customer account
> -> each customer may add his favourite products to this table
> -> the table contains the customer ID, and the product ID as well as
>some additional data (e.g. annual requirement, special prices, offers, ....)
>
>+--------------------------------
>| CUSTOMER_ID
>| PRODUCT_ID (refers to PRODUCT.ID)
>| ANNUAL_REQUIREMENT
>| ....
>+--------------------------------
>
>Table 2.: product table
> -> product data will be saved in this table. each product has an unique
>ID.
> -> each product has a 'parent product'. there is a column in the article
>table which refers to the parent product through the article id.
> -> the 'parent-products' are self-refering. (the parent product column
>contains the article id)
>
>+--------------------------------
>| ID (unique)
>| NAME
>| PARENT (refers to ID)
>| ....
>+--------------------------------
>
>Now I would like to get the customer account data for a specific customer,
>but the additional customer account data (e.g. annual requirement) should be
>taken from the parent-product and not the actual product itself. This query
>is currently working fine. However the problem is that sometimes the
>parent-product of a specific product isn't in the customer account table.
>In this cases the annual requirement and all the other data should be 0 or
>simply left empty.
>
>Here's my current sql query:
>
>SELECT
> C1.PRODUCT_ID,
> PRODUCT.NAME,
> C2.ANNUAL_REQUIREMENT
>FROM CUSTOMER_ACCOUNT C1, CUSTOMER_ACCOUNT C2, PRODUCT
>WHERE C1.CUSTOMER_ID = '#customer ID#'
>AND C2.CUSTOMER_ID = C1.CUSTOMER_ID
>AND C1.PRODUCT_ID = PRODUCT.ID
>AND PRODUCT.PARENT = C2.PRODUCT_ID (+)
>
>This query only retrieves the customer account rows which contain products
>whose parent products are also in the customer account.(of course all
>self-referencing parent products are also being retrieved) However it should
>return all customer account entries for the specific customer.
>
>regards
>maros
>
>from vienna/austria
>
>

For what class is this homework?

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)