CODEWITHSUNDEEP
mysqlsql
Shyam
Shyam

Reputation: 571

Join 4 tables through SQL with JOIN

Four tables like below, need to be joined to form one table.

Merchants Table

Date|Merchants|Date ID

Users Table

Date|Users|Date ID

Sales Table

Date|Sales|Date ID

Revenue Table

Date|Revenue|Date ID

Date and Date ID are pretty much the same. All these tables get the corresponding value for each day. But on a day, when there are new merchants, there need not be new users. On a day, when there is Sales and Revenue, there need not be new merchants.

But, when I try to use the usual SELECT query, with three LEFT JOINs, I do not get all the data.

How to include all dates from all four tables and have 0 when there is no data, for Sales, Users, etc.,?

It seems pretty much achievable but I'm not able to. :-/

Upvotes: 1

Views: 89

Answers (2)

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32003

you try below way

select t.*,m.Merchants,r.Revenue from
       (
        select u.dateID,s.Sales,u.Users form Users u inner join  Sales s on  
        u.dateID=s.dateID
      ) as t left join Merchants m ON t.dateid= m.Dateid
             left join Revenue r on t.dateid=r.dateid

Upvotes: 0

LukStorms
LukStorms

Reputation: 29647

You could LEFT JOIN them to a sub-query with all the Date ID.

SELECT 
s.Sales, 
m.Merchants, 
u.Users, 
r.Revenue,
f.`Date ID`
FROM 
(
  SELECT `Date ID` FROM Sales UNION
  SELECT `Date ID` FROM Merchants UNION
  SELECT `Date ID` FROM Users UNION
  SELECT `Date ID` FROM Revenue
) f
LEFT JOIN Sales s ON s.`Date ID` = f.`Date ID`
LEFT JOIN Merchants m ON m.`Date ID` = f.`Date ID`
LEFT JOIN Users u ON u.`Date ID` = f.`Date ID`
LEFT JOIN Revenue r ON r.`Date ID` = f.`Date ID`

Upvotes: 1

Related Questions