Mysql. Query of a detail table relating with two master tables, in an exclusive way-CodePudding

A table 'Detail' is related with two 'Master' tables by two fields, foreign keys, in an exclusive way. Either with Master1_ID or Master2_ID (only one of it will have the value of the id of the Maestro table to which it is related, the other will be NULL).

I want to get in a MySQL query the Detail records joined to the corresponding master.

Tables:

Detail[DetailID, DetailTitle, Master1_ID, Master2_ID, ...]
Master1[MasterID, MasterTitle, ...]
Master2[MasterID, MasterTitle, ...]

Master1_ID is the foreign key of the table Master1 and Master2_ID the foreign key of the table Master2. Only one of the two fields will have a value from the table it corresponds to.

And these are the records of the query I want to get:

Query: [DetailID, DetailTitle, MasterID, MasterTitle]

It will be appreciated to have in mind performance issues.

Thank you very much.

CodePudding user response：

I think you can just use left join and coalesce

SELECT d.DetailID, 
  d.DetailTitle, 
  COALESCE(m1.MasterID,m2.MasterID), 
  COALESCE(m1.MasterTitle,m2.MasterTitle)
FROM Detail d
LEFT JOIN Master1 m1 ON d.Master1_ID = m1.ID 
LEFT JOIN Master2 m2 ON d.Master2_ID = m2.ID

CodePudding user response：

For now, and if no one has a better idea, I think the solution is to use the UNION TABLES:

SELECT d.DetailID, d.DetailTitle, m1.MasterID, m1.MasterTitle FROM Detail d, Master1 m1
WHERE d.Master1_ID = m1.ID AND d.Master1_ID IS NOT NULL
UNION
SELECT d.DetailID, d.DetailTitle, m2.MasterID, m2.MasterTitle FROM Detail d, Master2 m2
WHERE d.Master2_ID = m2.ID AND d.Master2_ID IS NOT NULL

I leave the question open in case someone can contribute something more.