muaaz muaaz - 1 month ago 7
SQL Question

Select the used and not used Rows from Foreign key constraints

well I have the following tables.

components (prodNo, compNo, partNo)
parts (partNo, partName)
products (prodNo, prodName)


I need to select the parts that are being used OR not being used to manufacture the different product.

I can select the parts that are being used but dont know how to include the not used parts in the result.

Anyways, the result should be in following pattern

Result

prodNo, prodName, partNo, partName, compNo, status
101 A 23 G 55 YES
NULL NULL 20 I NULL NO
34 F 555 S 58 YES

Answer

You should use the parts table as the core of the query and make outer joins with the corresponding components and products tables

Select products.prodNo, products.prodName, parts.partNo, parts.partName, components.compNo,
case when NVL(products.prodNo,0) > 0 then 'YES' else 'NO' end as status
from parts 
left outer join components on components.partNo = parts.partNo
left outer join products on products.prodNo = components.prodNo