CODEWITHSUNDEEP
sqloracle-database
Hafim Hafim
Hafim Hafim

Reputation: 13

Single sql Query with getting Supplier Details

A - Supplier Table
B - Supplier Summary Table

I need a sql query which is suitable for this scenario.

If we have data in Supplier Summary Table show the SupplierId and SupplierName.
If not, show the SupplierId and SupplierName from Supplier Table.

SELECT A.SUPPLIER_ID,B.SUPPLIER_ID
    FROM BIZZXE_V2_SCH.SUPPLIERS A,
        (SELECT * FROM BIZZXE_V2_SCH.SUPPLIER_SUMMARY WHERE SUPPLIER_ID = 20)B
    WHERE  B.SUPPLIER_ID   = A.SUPPLIER_ID (+)

This is not giving proper Results. How to do it?

Upvotes: 1

Views: 1633

Answers (2)

MT0
MT0

Reputation: 167962

If we have data in Supplier Summary Table show the SupplierId and SupplierName. If not, show the SupplierId and SupplierName from Supplier Table.

SELECT COALESCE( B.SUPPLIER_ID, A.SUPPLIER_ID ) AS SUPPLIER_ID,
       COALESCE( B.SUPPLIER_NAME, A.SUPPLIER_NAME ) AS SUPPLIER_NAME
FROM   BIZZXE_V2_SCH.SUPPLIERS A
       LEFT OUTER JOIN
       BIZZXE_V2_SCH.SUPPLIER_SUMMARY B
       ON ( A.SUPPLIER_ID   = B.SUPPLIER_ID )
WHERE  A.SUPPLIER_ID = 20;

Upvotes: 1

Madhivanan
Madhivanan

Reputation: 13700

Can you try this?

SELECT A.SUPPLIER_ID,COALESCE(B.SUPPLIER_ID,A.SUPPLIER_ID)
FROM BIZZXE_V2_SCH.SUPPLIERS A LEFT JOIN BIZZXE_V2_SCH.SUPPLIER_SUMMARY B on
B.SUPPLIER_ID   = A.SUPPLIER_ID AND SUPPLIER_ID = 20

Upvotes: 0

Related Questions