Merging two tables without using join in mysql?

Question

I have done an unsuccessful attempt to merge values from two different tables.The first one displays just as I would like but the 2nd one displays only the first row every time.

Select * From (Select date as Date, Sum(qty) as qtySum, Sum(weight) 
as weightSum From stock_list Group by date) as A,
(Select Sum(weight) as weightSum,Count(barcode)
as barcodeCount From selected_items Group by date) as B Group by date;

This is the output that I get.

These is my selected_items table.

This is my stock_list.

Both my queries work individually and I get the correct output only when I try to run them together it gives a problem for the 2nd query.Can anyone point out my mistake or show me a better way to do it. This is what my final objective is

Why you don't use `JOIN` operator? Simply FULL JOIN results from table A and table B through date column. — fabulaspb, Jan 20 '16 at 13:05
If you want to achieve something, don't prevent yourself from doing so by putting up artificial barriers such as `no join`. — Dan Bracuk, Jan 20 '16 at 13:05
@Jens I tried it din work the way I wanted it to. This was what I used SELECT sl.date AS DATE, B.qtysum AS Stocksum, B.weightsum AS Stockweight, C.barcodeCount AS BilledItems, C.weightsum AS Billedweight FROM stock_list sl JOIN ( SELECT DATE, SUM( qty ) AS qtysum, SUM( weight ) AS weightsum FROM stock_list GROUP BY DATE ) AS B ON B.date = sl.date JOIN ( SELECT DATE, SUM( weight ) AS weightsum, COUNT( barcode ) AS barcodecount FROM selected_items GROUP BY DATE ) AS C ON C.date = sl.date; — AndroidNewBee, Jan 20 '16 at 13:06
@fabulaspb I did use join it din work for me can you please check my previous comment for my query. — AndroidNewBee, Jan 20 '16 at 13:08
@DanBracuk sorry but I tried and failed so I tried this and it nearly worked.Can you help with a solution.Thank you — AndroidNewBee, Jan 20 '16 at 13:10

score 1 · Accepted Answer · edited May 23 '17 at 12:14

The first problem is that you are grouping by date in your subquery B, but you don't select it, so your result set might be something like:

weightSum       barcodeCount
---------------------------
26              8
9               14
4               7

This is the result for 3 dates, but you have no idea which date which row refers to.

Your next problem is that you are using a cross join, because there is no link between your two queries, this means if your first query returns:

Date            qtySum      weightSum
----------------------------------------
2016-01-20      1           1
2016-01-21      2           2

After you have done this cross join you end up:

Date            qtySum      a.weightSum     b.weightSum     barcodeCount
--------------------------------------------------------------------------
2016-01-20      1           1               26              8
2016-01-20      1           1               9               14
2016-01-20      1           1               4               7
2016-01-21      2           2               26              8
2016-01-21      2           2               9               14
2016-01-21      2           2               4               7

So every row from A is matched with every row from B giving you 6 total rows.

Your third problem is that you then group by date, but don't perform any aggregates, without delving too much into the fine print of the SQL Standard, the group by clause, and functional dependency, lets simplify it to MySQL allows this, but you shouldn't do it unless you understand the limitations (This is covered in more detail on this in this answer). Anything in the select that is not in a group by clause should probably be within an aggregate.

So, due to MySQL's GROUP BY Extension by selecting everything and grouping only by date, what you are effectively saying is take 1 row per date, but you have no control over which row, it might be the first row from each group as displayed above, so the result you would get is:

Date            qtySum      a.weightSum     b.weightSum     barcodeCount
--------------------------------------------------------------------------
2016-01-20      1           1               26              8
2016-01-21      2           2               26              8

Which I think is why you are ending up with all the same values from the subquery B repeated.

So that covers what is wrong, now on to a solution, assuming there will be dates in stock_list that don't exist in selected_items, and vice versa you would need a full join, but since this is not supported in MySQL you would have to use UNION, the simplest way would be:

SELECT  t.Date,
        SUM(t.StockQuantity) AS StockQuantity, 
        SUM(t.StockWeight) AS StockWeight, 
        SUM(t.SelectedWeight) AS SelectedWeight, 
        SUM(t.BarcodeCount) AS BarcodeCount
FROM    (   SELECT  date, 
                    SUM(qty) AS StockQuantity, 
                    SUM(weight) AS StockWeight, 
                    0 AS SelectedWeight, 
                    0 AS BarcodeCount
            FROM    stock_list
            GROUP BY Date
            UNION ALL
            SELECT  date, 
                    0 AS StockQuantity, 
                    0 AS StockWeight, 
                    SUM(weight) AS SelectedWeight, 
                    COUNT(BarCode) AS BarcodeCount
            FROM    selected_items
            GROUP BY Date
        ) AS t
GROUP BY t.Date;

EDIT

I can't test this, nor am I sure of your exact logic, but you can use variables to calculate a running total in MySQL. This should give an idea of how to do it:

SELECT  Date,
        StockQuantity,
        StockWeight,
        SelectedWeight,
        BarcodeCount,
        (@w := @w + StockWeight -  SelectedWeight) AS TotalWeight,
        (@q := @q + StockQuantity - BarcodeCount) AS TotalQuantity
FROM    (   SELECT  t.Date,
                    SUM(t.StockQuantity) AS StockQuantity, 
                    SUM(t.StockWeight) AS StockWeight, 
                    SUM(t.SelectedWeight) AS SelectedWeight, 
                    SUM(t.BarcodeCount) AS BarcodeCount
            FROM    (   SELECT  date, 
                                SUM(qty) AS StockQuantity, 
                                SUM(weight) AS StockWeight, 
                                0 AS SelectedWeight, 
                                0 AS BarcodeCount
                        FROM    stock_list
                        GROUP BY Date
                        UNION ALL
                        SELECT  date, 
                                0 AS StockQuantity, 
                                0 AS StockWeight, 
                                SUM(weight) AS SelectedWeight, 
                                COUNT(BarCode) AS BarcodeCount
                        FROM    selected_items
                        GROUP BY Date
                    ) AS t
            GROUP BY t.Date
        ) AS t
        CROSS JOIN (SELECT @w := 0, @q := 0) AS v
GROUP BY t.Date;

the previous query was working with a little problem this one doesn't. — AndroidNewBee, Jan 20 '16 at 13:34
Are you able to elaborate on "not working"? Syntax error? Incorrect results? — GarethD, Jan 20 '16 at 13:37
Sorry it is working now but with a small problem please check this link http://i.stack.imgur.com/9gunD.png for 20 jan 2016 can you tell me why i get two records there also can you help me add total items and total weight for that day.Thank you so much :) — AndroidNewBee, Jan 20 '16 at 13:46
I am a bit baffled by that to be honest. Do either of your tables store the date with a time element to it? — GarethD, Jan 20 '16 at 13:50
No the tables are just like you can see in the image's but can you help me with a way to get the total items and total weight for a particular date. — AndroidNewBee, Jan 20 '16 at 13:53
Ooh I solved that it was a problem with the data now I just need the total items and total weight .How can I get that ? This is the weight and number of items at the end of that day.Can you help? — AndroidNewBee, Jan 20 '16 at 14:04

score 0 · Answer 2 · answered Jan 20 '16 at 13:10

You could use a join. However, if the set of dates is not the same in both tables, then you would want a full outer join. But that is not available in MySQL. Instead:

select date, sum(qtySum), sum(weightsum1), sum(weightsum2), sum(barcodeCount)
from ((Select date as Date, Sum(qty) as qtySum, Sum(weight) as weightSum1,
              NULL as weightsum2, NULL as barcodeCount
        From stock_list
        Group by date
       ) union all
       (Select date, null, null, Sum(weight), Count(barcode) as barcodeCount 
        From selected_items
        Group by date
       )
      ) t
Group by date;

I'm not sure how your desired output corresponds to the query you have provided. But this should aggregate and combine the data from the two tables by date, so you can finalize the query.

Thank you @GordonLinoff it worked how can i put a dash instead of NULL and can you help me getting the total items and total weight I am adding my table details.Thank you so much :) — AndroidNewBee, Jan 20 '16 at 13:20
@AndroidNewBee . . . You probably want to do that in the application. A dash is a string, whereas the returned values are numbers. If you want a dash, you need to be careful how you convert the numbers to strings. — Gordon Linoff, Jan 21 '16 at 04:22

score 0 · Answer 3 · answered Jan 20 '16 at 13:15

You can use FULL JOIN operator to solve your task if some dates may not exists in both tables.

Select ISNULL(A.date, B.date) AS date,
       A.qtySum,
       A.weightSum,
       B.weightSum,
       B.barcodeCount
From 
    (Select date as Date, 
            Sum(qty) as qtySum, 
            Sum(weight) as weightSum 
     From stock_list 
     Group by date) as A
FULL JOIN
(Select date,
        Sum(weight) as weightSum,
        Count(barcode) as barcodeCount 
 From selected_items 
 Group by date) as B ON A.date = B.date

Merging two tables without using join in mysql?

3 Answers3