Join two tables using mysql

Question

table:tab1

  id    date_time            zoneid  accountid  slotid     trequest  bidder width   height
_50832  2017-09-04 15:41:06  153     1654       153x468x60  10       aaa      468   60
_50832  2017-09-04 15:41:06  152     1654       152x468x60  10       bbb      468   60

table:tab2

 id     date_time             zoneid    accountid   slotid      bidder count    
_50832  2017-09-04 15:41:06   152       1654        152x468x60  bbb     6   
_50832  2017-09-04 15:41:06   152       1654        152x468x60  bbb     4   
_50832  2017-09-04 15:41:06   153       1654        153x468x60  aaa     9   
_50832  2017-09-04 15:41:06   153       1654        153x468x60  aaa     1

below is my query:

SELECT SUM(req.trequest) as REQ, SUM(win.count) as IMP
 FROM tab1  as req
JOIN tab2 as win ON (req.id=win.id AND req.zoneid=win.zoneid)
GROUP BY req.zoneid

I get below result,

REQ IMP
20  10
20  10

IMP count is correct but I get wrong REQ count. My expected result is

REQ IMP
10  10
10  10

How to get my expected result?

Why u doing SUM(req.trequest)? i think because of SUM(req.trequest) you got wrong result... — mayur panchal
– mayur panchal, Commented Sep 4, 2017 at 12:45

jophab · Accepted Answer · 2017-09-05 16:16:54Z

2

Lets find the sum of trequest and count separately based on zoneid and id.Then use these two results ( t1 and t2 ) in the inner join.

Count mismatch problem shown in the question occur due to multiple rows satisfying the joining conditions.

In this solution we will only have one entry for each zoneid in both the results ( t1 and t2 ). So the problem is avoided.

Note: You can remove the id column from the GROUP BY clause if it doesn't make any difference.

SELECT t1.id, t1.zoneid, t1.REQ, t2.IMP FROM

(SELECT id,zoneid,SUM(trequest) as REQ 
 FROM tab1  GROUP BY zoneid,id ) t1

INNER JOIN

(SELECT id,zoneid SUM(win.count) as IMP
 FROM tab2  GROUP BY zoneid,id ) t2

ON t1.id = t2.id 
AND t1.zoneid = t2.zoneid

edited Sep 5, 2017 at 16:16

answered Sep 4, 2017 at 13:55

jophab

5,54714 gold badges45 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Stanislovas Kalašnikovas · Accepted Answer · 2017-09-04 12:52:59Z

2

Let's try first sumwin.count and group records in sub-query, after it join tables. Try in following:

SELECT SUM(req.trequest) as REQ, SUM(win.count) as IMP
FROM tab1 as req
JOIN (
    SELECT SUM(win.count) as IMP, win.zoneid, win.id 
    FROM tab2 as win 
    GROUP BY win.zoneid, win.id) AS win ON req.id=win.id AND req.zoneid=win.zoneid
GROUP BY req.zoneid

answered Sep 4, 2017 at 12:52

Stanislovas Kalašnikovas

9,0735 gold badges36 silver badges60 bronze badges

Comments

echology · Accepted Answer · 2017-09-04 13:44:37Z

0

Instead of req.zoneid. You should try win.zoneid. What seems is that the rows in table 1 are counted multiple times as zoneid in table 2 comes twice. So win.zoneid would group it and avoid the repetition.

Updated: The solution posted by @mayur panchal is the correct one as you don't need to SUM the rows in first table as they belong to different zoneid. If you SUM them you will obviously get the 20 repeated twice.

edited Sep 4, 2017 at 13:44

answered Sep 4, 2017 at 13:03

echology

3972 gold badges3 silver badges15 bronze badges

Collectives™ on Stack Overflow

Join two tables using mysql

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related