Problemt with mysql join

Question

I have a problem with the query shown below. It always returns the same value for both snapshot and adm. Also the values returned are not what I woluld expect.

SELECT 
domains.name, count(snapshot_info.snapshot_uuid) as snapshot, count(users_domains.uuuid) as adm
FROM 
domains, snapshot_info, users_domains
WHERE 
domains.duuid = snapshot_info.duuid 
AND 
users_domains.duuid = domains.duuid 
group by name;

Returns:

domain1 33 33
domain2 40 40
domain3 3  3

This query works and returns the correct values

SELECT 
domains.name, count(snapshot_info.snapshot_uuid) as snapshot
FROM 
domains, snapshot_info
WHERE 
domains.duuid = snapshot_info.duuid 
group by name;

Returns

domain1 3
domain2 20
domain3 17

So, as you can see, the problem are introduceed when I add the users_domains join.

Any suggestions?

It would be useful to have a sample dataset from each table and a general table structure. It's kind of hard to debug the SQL when we don't know what the structure looks like and what the expected result should be. — xJoshWalker, Oct 24 '14 at 15:20
Have you tried running just the second arguments of your query? Such as joining snapshot_info to user_domains — xJoshWalker, Oct 24 '14 at 15:21

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

1

It's better to use this syntax (see this question: INNER JOIN vs WHERE clause):

SELECT 
  domains.name,
  count(DISTINCT snapshot_info.snapshot_uuid) as snapshot,
  count(DISTINCT users_domains.uuuid) as adm
FROM 
  domains INNER JOIN snapshot_info
  ON domains.duuid = snapshot_info.duuid
  INNER JOIN users_domains
  ON users_domains.duuid = domains.duuid 
GROUP BY
  name;

and notice that you should use COUNT(DISTINCT ...) in this case.

edited May 23 '17 at 12:27

Community

1
1

answered Oct 24 '14 at 15:27

fthiella

48,073
15
90
106

Thank you. The problem was that I was missing "distinct" in my query. – TheG Oct 24 '14 at 15:35

carexcer · Answer 2 · 2014-10-24T15:40:31.450

0

In the first query, you are doing a CROSS JOIN between domains, snapshot_info, users_domains.

If the second query runs right for you, maybe you are duplicating rows between domains and users_domains, so you will need another field to join these table to don't duplicate, because the join users_domains.duuid = domains.duuid is insufficient.

However, try this:

SELECT 
     domains.name,
     count(distinct snapshot_info.snapshot_uuid) as snapshot,
     count(distinct users_domains.uuuid) as adm
FROM 
     users_domains
     LEFT OUTER JOIN 
     domains 
     ON users_domains.duuid = domains.duuid 
     LEFT OUTER JOIN
     snapshot_info
     ON domains.duuid = snapshot_info.duuid 
group by name;

edited Oct 24 '14 at 15:40

answered Oct 24 '14 at 15:24

carexcer

1,407
2
15
27

Thank you! This query is correct, exept that it returns the same values as my original query. The problem is missing "distinct". See accepted answer below. – TheG Oct 24 '14 at 15:37
Ok, if you want only distinct values you're right. Thanks by appreciation – carexcer Oct 24 '14 at 15:40

Problemt with mysql join

2 Answers2