Search code examples
mysqlsqljoinleft-joinmysql-num-rows

mysql left join returns unexpected amount of rows

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

Solution

  • 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