muaaz muaaz - 11 months ago 46
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


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

Answer Source

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