Question 21016 in Mysql Question posted in
The official MySQL documentation can be found here.

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

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

Tags:

2

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 0

    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search