Re: Help with SQL.

From: Roelof <r.pit_at_raadvanstate.nl>
Date: 26 Sep 2002 01:07:59 -0700
Message-ID: <f602866b.0209260007.7a4e2306_at_posting.google.com>

Hi Peter

Try this :

SELECT T_PRODUCT.PRODUCT_ID,T_PRODUCT.PRODUCT_NAME,t_productstatus.status_id FROM T_PRODUCT, t_productstatus
WHERE
NVL(t_productstatus.status_id,t_product.product_id)=t_product.product_id AND

T_PRODUCT.PRODUCT_ID IN (SELECT T_PRODUCTCATEGORY.PRODUCT_ID FROM
T_PRODUCTCATEGORY,T_CATEGORY WHERE
T_PRODUCTCATEGORY.CATEGORY_ID=T_CATEGORY.CATEGORY_ID

AND T_CATEGORY.COUNTRY_ID = ?
AND T_CATEGORY.LANGUAGE_ID = ? )
ORDER BY PRODUCT_NAME If this does not help try decoding the null value in the select clause.

Greetings,

Roelof

"Peter Kirk" <peter_kirk_at_alpha-gruppen.dk> wrote in message news:<3d906ec6$0$62717$edfadb0f_at_dspool01.news.tele.dk>...
> Hi there,
>
> I have these tables:
>
> T_PRODUCT (product_id, product_name)
> T_PRODUCTCATEGORY (product_id, category_id)
> T_CATEGORY (category_id, country_id, language_id)
> T_PRODUCTSTATUS (product_id, country_id, language_id, status_id)
>
> Where a product (T_PRODUCT) is always linked to at least one category
> (T_CATEGORY) via T_PRODUCTCATEGORY; and a product may have 0 or 1 statuses
> as defined in T_PRODUCTSTATUS.
>
> A category also has a "country" and a "language". (So a product belongs to a
> country and language, or several countries and languages, via the categories
> it belongs to).
>
> How do I find all the data for all products belonging to a particular
> country and language?
> That is, a result-set with these data:
>
> product_id, product_name, status_id?
>
> This gives me a list of all products in a particular country and language,
> but I don't know how to add the statuses:
> SELECT T_PRODUCT.PRODUCT_ID,T_PRODUCT.PRODUCT_NAME
>
> FROM T_PRODUCT
>
> WHERE
>
> T_PRODUCT.PRODUCT_ID IN (SELECT T_PRODUCTCATEGORY.PRODUCT_ID FROM
>
> T_PRODUCTCATEGORY,T_CATEGORY WHERE
>
> T_PRODUCTCATEGORY.CATEGORY_ID=T_CATEGORY.CATEGORY_ID
>
> AND T_CATEGORY.COUNTRY_ID = ?
>
> AND T_CATEGORY.LANGUAGE_ID = ? )
>
> ORDER BY PRODUCT_NAME
>
>
> The problem I have is that I don't know how to "ignore" the possibility that
> there is no status in T_PRODUCTSTATUS for a particualr product (in which
> case I would like the result-set to contain "null" for the status_id - or a
> default value of 1 or something).
>
> Thanks for any help,
> Peter
Received on Thu Sep 26 2002 - 10:07:59 CEST