Select multiple values via CASE statement?
I have a Vehicle table with an Owner_ID field in it.开发者_StackOverflow社区 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"
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
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`
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
精彩评论