CODEWITHSUNDEEP
mysqlselectjoincoalesce
Wonka
Wonka

Reputation: 8684

MySql - Retrieve correct values using COALESCE?

I have an sql fiddle here: 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

Upvotes: 1

Views: 148

Answers (2)

Andrew
Andrew

Reputation: 4624

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.

Upvotes: 2

gcochard
gcochard

Reputation: 11744

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

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

Upvotes: 1

Related Questions