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
– xJoshWalker, Commented Oct 24, 2014 at 15:20
Have you tried running just the second arguments of your query? Such as joining snapshot_info to user_domains — xJoshWalker
– xJoshWalker, Commented Oct 24, 2014 at 15:21

Community · Accepted Answer · 2017-05-23 12:27:21Z

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, 2017 at 12:27

CommunityBot

11 silver badge

answered Oct 24, 2014 at 15:27

fthiella

49.2k15 gold badges96 silver badges108 bronze badges

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

1 Comment

TheG Over a year ago

Thank you. The problem was that I was missing "distinct" in my query.

carexcer · Accepted Answer · 2014-10-24 15:40:31Z

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, 2014 at 15:40

answered Oct 24, 2014 at 15:24

carexcer

1,4452 gold badges18 silver badges27 bronze badges

2 Comments

TheG Over a year ago

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.

carexcer Over a year ago

Ok, if you want only distinct values you're right. Thanks by appreciation

Collectives™ on Stack Overflow

Problemt with mysql join

2 Answers 2

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related