CODEWITHSUNDEEP
sqldatabaseselectcase
Delicate Hiba
Delicate Hiba

Reputation: 63

How to use CASE Statement for Multiple Select Statements in SQL

i have 3 Tables named as Vendor, Customers and ReceivePayment. in my ReceivePayment Table i have column named PaymentType and AccountID. all i want is to Select data from Vendor or customer table by using AccountID in ReceivePayment table but i have to apply condition either select data from Customer table or vendor table and condition will be based on PaymentType column of ReceivePayment table. like if i have 'Sale' in PaymentType column then it should select data from Customers table or if i have 'Purchase' in PaymentType column then it should select data from Vendor table.

I am using case statement but i don't know how to use Select statement in THEN Clause of case statement.

Screenshot of ReceivePayment Table

i am trying to use this code

    SELECT CASE ReceivePayment.PaymentType

    WHEN  'Sale' THEN SELECT Name FROM Vendor WHERE VendorID = ReceivePayment.AccountID
    WHEN  'Purchase' THEN SELECT Name FROM Customers WHERE CustID = ReceivePayment.AccountID
END
FROM ReceivePayment

Upvotes: 2

Views: 11745

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269703

You are quite close:

SELECT (CASE rp.PaymentType
            WHEN  'Sale'
            THEN (SELECT v.Name FROM Vendor v WHERE v.VendorID = rp.AccountID)
            WHEN  'Purchase'
            THEN (SELECT c.Name FROM Customers c WHERE c.CustID = rp.AccountID)
        END)
FROM ReceivePayment rp;

You just need parentheses around the subqueries. Note: You need to be sure that the subqueries only return 0 or 1 row, or you will get an error.

Note that I also added table aliases to simplify the query.

Upvotes: 3

Stanislovas Kalašnikovas
Stanislovas Kalašnikovas

Reputation: 9053

You could try to use conditional join and COALESCE something like this:

SELECT COALESCE(v.Name, C.Name, '') AS Name
FROM ReceivePayment AS rp
LEFT JOIN Vendor AS v ON rp.AccountID = v.VendorID AND rp.PaymentType = 'Sale'
LEFT JOIN Customers AS c ON rp.AccountID = c.CustID AND rp.PaymentType = 'Purchase'

Upvotes: 0

Related Questions