CODEWITHSUNDEEP
mysqlsql
Aravind raj c k
Aravind raj c k

Reputation: 13

How to join 3 table and perform union in a subquery?

There are four table Bill_entry,Customer,Chit,Cash. I want to join table Customer with table Bill_entry in following query where they have common column customer_id. Goal here is by using customer_id i want to print customer_name too in one query.

i have tried but couldn't get correct syntax

Initial code before including Customer table :

SELECT Bill_entry.*
FROM (SELECT * FROM Chit UNION SELECT * FROM Cash) as t1 RIGHT JOIN 
     entry
     ON (Bill_entry.bill_no = t1.bill_no)
WHERE t1.bill_no IS NULL

MY tries :

SELECT Bill_entry.*, Customer.customer_name
FROM ((SELECT * FROM Chit UNION SELECT * FROM Cash) as t1 RIGHT JOIN entry ON (Bill_entry.bill_no = t1.bill_no) WHERE t1.bill_no IS NULL)customer where Bill_entry.customer_id = Customer.Customer_id

Upvotes: 1

Views: 34

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269933

Just add in another JOIN:

SELECT e.*, cu.customer_name
FROM bill_entry e LEFT JOIN
     (SELECT * FROM Chit
      UNION ALL  -- assume you don't want to remove duplicates
      SELECT * FROM Cash
     ) c 
     entry e
     ON e.bill_no = c.bill_no LEFT JOIN
     Customer cu
     ON cu.customer_id = e.Customer_id
WHERE c.bill_no IS NULL;

Note some changes.

  • The UNION --> UNION ALL. I assume you don't want to remove duplicates or incur the overhead for trying to remove them.
  • RIGHT JOIN --> LEFT JOIN. It is usually much simpler to think about LEFT JOINs -- keep all the rows in the first table and then matching rows in the others.
  • The JOIN conditions are all in ON clauses, not the WHERE clause.

Upvotes: 1

Related Questions