Take value if exist in subquery put default value if not exist-CodePudding

I try to achieve a particular mysql query but I didn't figure out. Imagine you have a collection with name and age kitty. It could looks like that :

The thing I want, is when I found the combination value name and age are found in ref_name ref_age , I take the cat breed, and for others I put European breed default (yes the example is weird but you got the idea)

In the end with the sample above, it should give this result

I tried to write a query looks like this :

SELECT *, CASE WHEN EXISTS(SELECT * FROM cats as a
inner join cats as b
on a.name = b.ref_name
and a.age = b.ref_age) THEN a.breed ELSE "European" END as breed
FROM cats
order by breed

But "European" seems never been set... I tried with an IF without no more success

Feel like I'm not very far from what I'd like but not satisfied

Do you have an idea on how to achieve that ?

CodePudding user response：

You can make use of LEFT OUTER JOIN to join cats A with cats B with condition A.name = B.ref_name AND A.age = B.ref_age. Those records on table B would go NULL when a matching record does not exist in A. Later, make use of COALESCE function which would select the first NON-NULL parameter:

SELECT a.name, a.age, COALESCE(b.breed, 'European') FROM cats AS a LEFT OUTER JOIN cats AS b ON (a.name = b.ref_name and a.age = b.ref_age)