MySql - 使用COALESCE检索正确的值？

I have an sql fiddle here: http://www.sqlfiddle.com/#!2/fbd48/1

我这里有一个sql小提琴：http：//www.sqlfiddle.com/#！2 / fbd48 / 1

The colors should be red for flower and brown for bear, but it shows red for both.

花的颜色应为红色，熊的颜色应为棕色，但两者的颜色均为红色。

Not sure if COALESCE fits here, but was in the accepted answer for something similar here: MySQL - Retrieve row value from different table depending on value of row in a table

不确定COALESCE是否适合这里，但是在这里类似的东西是接受的答案：MySQL - 根据表中行的值从不同的表中检索行值

2 个解决方案

#1

SELECT mem.member_name, g.*
, coalesce(f.flower_color, b.bear_color) as color
from members mem
inner join general g on mem.member_id = g.member_id
left join flowers f on g.gift_item_id = f.flower_id AND g.gift_item = 'flower'
left join bears b on g.gift_item_id = b.bear_id AND g.gift_item = 'bear'
WHERE g.month='june'

There was no way to differentiate a flower from a bear, so I added tests to the joins. This is pretty smelly. you might rethink your schema.

没有办法区分花和熊，所以我添加了测试加入。这很臭。您可能会重新考虑您的架构。

#2

It seems to work if you use unique gift item IDs.

如果您使用独特的礼品ID，它似乎有效。

http://www.sqlfiddle.com/#!2/c02c8/1

#1

SELECT mem.member_name, g.*
, coalesce(f.flower_color, b.bear_color) as color
from members mem
inner join general g on mem.member_id = g.member_id
left join flowers f on g.gift_item_id = f.flower_id AND g.gift_item = 'flower'
left join bears b on g.gift_item_id = b.bear_id AND g.gift_item = 'bear'
WHERE g.month='june'