CODEWITHSUNDEEP
mysql
anothershrubery
anothershrubery

Reputation: 21003

Select multiple values via CASE statement?

I have a Vehicle table with an Owner_ID field in it. This Owner_ID will correspond to either the ID field in a Customer table or the ID field in a Business table. I am looking to return different values depending on the table that the ID belongs to.

I have this statement:

SELECT v.Make, v.Model
FROM Vehicle v
LEFT JOIN Customer c ON c.ID = v.Owner_ID
LEFT JOIN Business b ON b.ID = v.Owner_ID

So say I want to return the customer surname if the Owner_ID = Customer.ID, and the business name if Owner_ID = Business_ID. I know I can use the CASE statement as such:

SELECT v.Make, v.Model,
    CASE WHEN c.ID IS NOT NULL THEN c.Surname
        WHEN b.ID IS NOT NULL THEN b.Name

But is there a way to get multiple values from one case statement? Something like: (I know this is completely wrong by the way.)

SELECT v.Make, v.Model,
    CASE WHEN c.ID IS NOT NULL THEN
            SELECT c.Surname, c.Date_Of_Birth
        WHEN b.ID IS NOT NULL THEN
            SELECT b.Name, b.Founded

Or will I have to do:

SELECT v.Make, v.Model,
    CASE WHEN c.ID IS NOT NULL THEN c.Surname
        WHEN b.ID IS NOT NULL THEN b.Name
    END AS "Name",
    CASE WHEN c.ID IS NOT NULL THEN c.Date_Of_Birth
        WHEN b.ID IS NOT NULL THEN b.Founded
    END AS "DOB/Founded"

Upvotes: 7

Views: 6069

Answers (3)

Martin Smith
Martin Smith

Reputation: 453192

No there is no way to get a branch of a case expression to span multiple columns.

In your example case you could do

SELECT v.Make,
       v.Model,
       MAX(COALESCE(c.Surname, b.name)) as name
FROM   Vehicle v
       LEFT JOIN Customer c
         ON c.ID = v.Owner_ID
       LEFT JOIN Business b
         ON b.ID = v.Owner_ID
GROUP  BY v.Owner_ID

Upvotes: 2

jimy
jimy

Reputation: 4908

Not Sure, but you can try this one 

SELECT v.Make, v.Model
FROM Vehicle v
LEFT JOIN Customer c ON c.ID = v.Owner_ID
LEFT JOIN Business b ON b.ID = v.Owner_ID 
WHERE c.Surname IS NOT NULL 
OR b.name IS NOT NULL

Upvotes: 0

Czechnology
Czechnology

Reputation: 14992

I think it needs to be clear what columns the query should return and this cannot change depending on the query. So the conditions have to be broken up per column.

What about IF? Might be easier to read in this case.

SELECT v.Make, v.Model,
    IF(c.ID IS NOT NULL, c.Surname,       IF(b.ID IS NOT NULL, b.Name,    NULL)) as `Name`
    IF(c.ID IS NOT NULL, c.Date_Of_Birth, IF(b.ID IS NOT NULL, b.Founded, NULL)) as `DOB_Founded`

Upvotes: 2

Related Questions