sql - Multiple Reason Tables used as a definition for 1 Table with one reason Column

Question

I have a requirement I have 1 table which has a container with a qty and a Reason_ID.

Problem is that there are 5 different Reason tables that can define the reason but the container table has only one column. So the container table looks like the follow:

Container Name	Qty	ReasonID	ContainerID
A222	50	LR111	AAA
A888	75	LR222	BBB
B333	32	BR111	CCC
B555	44	SR111	DDD
C777	77	BR333	EEE

question from:https://stackoverflow.com/questions/66065085/multiple-reason-tables-used-as-a-definition-for-1-table-with-one-reason-column

Answer 1

You that three reference tables in one column. You can use multiple left joins:

select c.*,
       coalesce(lr.definition, br.definition, sr.definition) as reason
from containers c left join
     loss_reasons lr
     on c.reasonid = lr.reasonid left join
     bonus_reasons br
     on c.reasonid = br.reasonid left join
     scrap_reasons sr
     on c.reasonid = sr.reasonid;

I would recommend that you rethink the data model. Put all the reasons into a single table with a "category" for the reason.