Re: Help with SQL.

"Peter Kirk" <peter_kirk_at_alpha-gruppen.dk> wrote in message news:<3d906ec6$0$62717$edfadb0f_at_dspool01.news.tele.dk>...
> 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).

Hi

Do not use an subselect! You use an outer join. (+)

This should do the trick:

SELECT P.PRODUCT_ID
, P.PRODUCT_NAME

FROM T_PRODUCT P
,    T_PRODUCTCATEGORY PC
,    T_CATEGORY  C
,    T_PRODUCTSTATUS PS

AND   P.PRODUCT_ID = PS.PRODUCT_ID (+)-- This is an outer join
AND   C.COUNTRY_ID = ??
AND   C.LANGUAGE_ID = ??