Help with SQL.
Date: Tue, 24 Sep 2002 15:55:23 +0200
Message-ID: <3d906ec6$0$62717$edfadb0f_at_dspool01.news.tele.dk>
Hi there,
I have these tables:
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?
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
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)
That is, a result-set with these data:
Thanks for any help,
Peter
Received on Tue Sep 24 2002 - 15:55:23 CEST