sql inner join?

From: Maros Kollar <spam_at_k-1.com>
Date: Sat, 6 Sep 2003 11:41:09 +0200
Message-ID: <3f59ab9c$0$29344$3b214f66@usenet.univie.ac.at>

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 Received on Sat Sep 06 2003 - 04:41:09 CDT