mysql left join returns unexpected amount of rows

Question

I have 2 tables where

tableA has 41 rows

and

tableB has 3 rows

I am trying to get the total rows of these 2 tables via a query using left join but i get way more rows(123) than expected(44)

query:

SELECT COUNT(*)
    FROM tableA as u
LEFT JOIN tableB as d
    ON u.uid=d.uid
WHERE
    u.uid=912391178669
    AND
    u.deleted = 0
    AND
    d.deleted=0

tables schema:

tableA

id | uid | deleted

tableB

id | uid | deleted

Addicted · Accepted Answer · 2012-04-27T09:04:12.070

2

I have run the following query It is working correctly.. U can check it out.

SELECT 
  ( SELECT count(*) from table1 where.... )
+ ( SELECT count(*) from table2 where.... )
as total from dual

edited Apr 27 '12 at 09:04

answered Apr 27 '12 at 05:43

Addicted

1,694
1
16
24

I believe it would be as correct without `SUM()`. And I'm not sure if MySQL supports `FROM DUAL`, but I know for certain that you can omit it. – Andriy M Apr 27 '12 at 08:18
ohhh ya actually I was trying two query at the same time so I think i mixed it up... yes without sum also its working... – Addicted Apr 27 '12 at 09:02

score 1 · Answer 2 · answered Apr 27 '12 at 05:00

1

I'm guessing that you have three rows in tableA with the uid given in the query. That will mean that each row in tableA will join once with each row in tableB, which means you will back 41 x 3 rows or 123.

From the number of rows you are expecting back, I wonder if you need a Union instead of a join.

Select * from tableA where uid = 912391178669 and deleted = 0

union all

Select * from tableB where uid = 912391178669 and deleted = 0

A union will combine the results of two queries. A join will combine the columns of table tables in a single query.

answered Apr 27 '12 at 05:00

Jeff Siver

7,434
30
32

my edited query looks like : Select count(id) as total from tableA where uid = 912391178669 and deleted = 0 union all Select count(id) as total from tableB where uid = 912391178669 and deleted = 0 but when i run it in phpmyadmin i get total|41|3 when i run it in on a script it shows just 41 returned back... how can i make it sure the sum? – stergosz Apr 27 '12 at 05:06
@fxuser: I think Jeff's idea was to lead you to something like this: `SELECT COUNT(*) FROM ( /* the UNION query here */ ) somealias`. – Andriy M Apr 27 '12 at 08:17

Pranav · Answer 3 · 2012-04-27T05:06:25.070

0

41*3=123

each row of TableA has uid=912391178669 and tableB each row also have uid that's why you are getting 123 row total. use some filter criteria to get desired result (like some AND condition)

if you can show us your table column then it may be possible to help you .

Left join does not combine the rows of two table .
TableA left join TableB will give you all the row of table A meeting the joining condition.

edited Apr 27 '12 at 05:06

answered Apr 27 '12 at 05:00

Pranav

8,563
4
26
42

i have added the schema on the question – stergosz Apr 27 '12 at 05:14

score 0 · Answer 4 · answered Apr 27 '12 at 05:37

0

SELECT COUNT(*)
FROM tableA as u
LEFT JOIN tableB as d
ON u.uid=d.uid
AND
u.deleted = d.deleted
WHERE
u.uid=912391178669
AND u.deleted = 0

answered Apr 27 '12 at 05:37

Muhammad Raheel

19,823
7
67
103

score 0 · Answer 5 · answered Apr 27 '12 at 05:54

0

SELECT SUM(
  (SELECT count(*) from tableA WHERE uid=912391178669)
+ (SELECT count(*) from tableA WHERE uid=912391178669)
 ) as totalRows

answered Apr 27 '12 at 05:54

Nesim Razon

9,684
3
36
48

mysql left join returns unexpected amount of rows

5 Answers5

Linked