aBennouna aBennouna - 2 months ago 5
MySQL Question

Join two tables in one table using SQL

Good evening,

I have an issue trying to find a solution to join two tables :

For example I have

Table 1 :

Date | Item
12/03 | aaaa
12/03 | aaaa
14/03 | bbbb
14/03 | aaaa
15/03 | cccc


Table 2 :

Date | Item2
11/03 | aaaa
12/03 | aaaa
13/03 | bbbb
14/03 | aaaa
15/03 | cccc


I want to do a count to have this

Date | Count(Item1) | Count(Item2)
11/03 | 0 | 1
12/03 | 2 | 1
13/03 | 0 | 1
14/03 | 2 | 1
15/03 | 1 | 1


I have tried this so far, but it doesn't seems to work, it only give me the commun dates :

SELECT F.DATE, COUNT(T1.Item1), COUNT(T2.Item2) FROM TABLE1
T1 LEFT JOIN TABLE2 T2 ON T1.date=T2.date

Date | Count(Item1) | Count(Item2)
12/03 | 2 | 1
14/03 | 2 | 1
15/03 | 1 | 1


Any help ?

Thanks

Answer
In the below SQL, the first inline view is computing all the counts at date level and same applies with second query. By combining the results of left join and right join between these two inline views we can combine all the common and uncommon results from both tables as you described.


        SELECT Date,
               Item1,
               Item2 
          FROM
             (  
                SELECT T1.Date AS Date,
                       COUNT(  T1.Item  ) AS Item1,                     
                       COUNT(  T2.Item  ) AS Item2  
                  FROM Table1 t1
                LEFT JOIN
                       Table2 t2  
                    ON t1.date = t2.date  
                GROUP BY t1.Date              
          UNION              
                SELECT T2.Date AS Date,
                       COUNT( T1.Item ) AS Item1,
                       COUNT( T2.Item ) AS Item2
                  FROM Table2 t2
                LEFT JOIN
                       Table1 t1   
                    ON t2.date = t1.date
                GROUP BY t2.Date
              ) A
         ORDER BY Date        
        ;

SQL Fiddle Implementation :-

http://sqlfiddle.com/#!9/5b872/14

Comments