How to Join three tables with one reference in the query in MySQL-CodePudding

Hi is there a way to join three tables like this but I can't change the reference of the column code.id at the query because I am creating a dynamic query for a search filter for this reference

Here is the code looks like

there are 4 tables bill, expense_distribution, item_distribution, project_code

columns in the bill - id, name, ...etc columns in the expense_distribution - id, bill_id, project_code_id, ...etc columns in the item_distribution - id, bill_id, project_code_id, ...etc columns in the project_code - id, name, ...etc

SELECT b.id, code.name FROM bill b 
LEFT JOIN expense_distribution exp ON b.id=exp.bill_id
LEFT JOIN project_code code ON exp.project_code_id=code.id
LEFT JOIN item_distribution itm ON b.id=itm.bill_id
LEFT JOIN project_code code ON itm.project_code_id=code.id

I can't use the query with two times project_code code but I want code.id for both item and expense distributions because of the filter.

can someone guide me for the best approach to do that, I am using JPQL for the code in Java

CodePudding user response：

There are multiple ways to approach this. One way you can achieve this is by providing different aliases for same table and coalesce the fields.

SELECT b.id, COALESCE(c1.name, c2.name) as name FROM bill b 
LEFT JOIN expense_distribution exp ON b.id=exp.bill_id
LEFT JOIN item_distribution itm ON b.id=itm.bill_id
LEFT JOIN project_code c1 ON exp.project_code_id=c1.id
LEFT JOIN project_code c2 ON itm.project_code_id=c2.id;

Another approach would be, change the firsy and last two lines to

SELECT b.id, code.name FROM bill b 
LEFT JOIN expense_distribution exp ON b.id=exp.bill_id
LEFT JOIN item_distribution itm ON b.id=itm.bill_id
LEFT JOIN project_code code ON COALESCE(itm.project_code_id, exp.project_code_id)=code.id;

Third, change the last line from above to

LEFT JOIN project_code c1 ON exp.project_code_id=code.id OR itm.project_code_id=code.id

CodePudding user response：

This is the tables creation:

 CREATE TABLE bill(
 id int(11),
 name varchar(10)
 ); 
 CREATE TABLE project_code(
 id int(11),
 name varchar(10)
 );
 CREATE TABLE expense_distribution(
 id int(11),
 bill_id int(11),
 project_code_id int(11),
 name varchar(10)
 );
 CREATE TABLE item_distribution(
 id int(11),
 bill_id int(11),
 project_code_id int(11),
 name varchar(10)
 );

Based on these tables you can query the following query:

SELECT b.id, codes.name FROM bill b 
LEFT JOIN expense_distribution exp ON b.id=exp.bill_id
LEFT JOIN project_code codes ON exp.project_code_id=codes.id
LEFT JOIN item_distribution itm ON b.id=itm.bill_id and itm.project_code_id =codes.id