CODEWITHSUNDEEP
mysqlsqlleft-join
Sjoerd de Wit
Sjoerd de Wit

Reputation: 2413

Sql count returns wrong numbers

i have an api which i make calls to and i need alot of data from different tables so i use joins on them, now the problem is that whilst i have 4 replies, and 5 interactions the data always returns 20 replies and 20 interactions this is the result: 

screen_name screen_state replies interactions alerts
sjerd       0            20      20           0

i use this query to count the records and results:

   SELECT u.screen_name,
          u.screen_state,
          count(r.id) AS replies,
          count(i.id) AS interactions,
          count(a.alerts) AS alerts
   FROM   users u 
   LEFT   JOIN reply r ON u.id = r.user 
   LEFT   JOIN interactions i ON u.id = i.user_id 
   LEFT   JOIN alerts a ON u.id = a.user_id WHERE u.id ='2' 
   GROUP  BY u.id, u.screen_state

can someone see why it's returning 20 while i only have 7 rows of replies in total in reply table, and 5 rows of interactions in total in interaction table. each row is 1 reaction or reply.

Upvotes: 2

Views: 1857

Answers (2)

Mohsen Heydari
Mohsen Heydari

Reputation: 7284

   SELECT u.screen_name, u.screen_state, 
   count(DISTINCT r.id) AS replies,
   count(DISTINCT i.id) AS interactions,
   count(DISTINCT a.alerts) AS alerts 
   FROM users u 
   LEFT JOIN reply r ON u.id = r.user 
   LEFT JOIN interactions i ON u.id = i.user_id 
   LEFT JOIN alerts a ON u.id = a.user_id WHERE u.id ='2' 
   GROUP BY u.id, u.screen_state

Upvotes: 1

Raad
Raad

Reputation: 4648

Your counts are always going to give the same result as all tables are joined at the same level.

You need to do your counts as inline sub-queries (or whatever - I can never remember the correct terminology):

SELECT u.screen_name,
       u.screen_state,
       (select count(*) from reply r where u.id = r.user) AS replies,
       (select count(*) from interactions i where u.id = i.user_id) AS interactions,
       (select count(*) from alerts a where u.id = a.user_id) AS alerts
FROM   users u
WHERE u.id ='2'

Upvotes: 3

Related Questions