MySQL Inner Join Returning Multiples of the Same Row

Question

I have two MySql Tables as follows:

resource
-----------------------------------------------
id   name           group   owner_id
-----------------------------------------------
1    MyResource1    hs      11
2    MyResource2    ms      24
3    MyResource3    ps      11
...

resource_access
-----------------------------------------------
id   resource_id    user_id
-----------------------------------------------
1    1              12
2    2              24
3    2              11
4    3              15
...

Now, the first table is a list of resources, of course, and their respective owners in the owner_id column. The second table is the result of "sharing" this resource with another user. The table resource_access may contain records with a user_id that is equivalent to the owner_id in a row of the resource_access as a result of messy cleanup from an owner exchange.

I simply want to get the id, name, and group of any resource that a user has access to, whether they are the owner or it has been shared with them. Here is my MySQL query for an example user (24):

SELECT resource.id, resource.name, resource.group 
FROM `resource` 
INNER JOIN resource_access ON (
    resource.owner_id='24' 
    OR (
        resource_access.user_id='24' AND 
        resource_access.resource_id=resource.id
    )
)

Right now, it returns the id, name, and group for resource number 2 multiple times (like twelve). Is there a possible cause for this? I have tried LEFT and RIGHT joins and am getting the same result. There are many records in the resource table, but none with the id of 2. There are no duplicate rows in resource_access sharing the resource with the same user twice.

Thanks in advance.

Use `SELECT DISTINCT` to remove duplicates. Otherwise, you get a row for each user that's sharing with them, because `JOIN` performs a cross-product between the two tables. — Barmar, Jun 29 '14 at 22:44

Barmar · Accepted Answer · 2014-06-29T22:54:26.987

7

Use:

SELECT DISTINCT resource.id, resource.name, resource.group

to remove duplicates.

The way an inner join conceptually works is that it produces a full cross-product between the two tables. This cross-product contains a row for each pair of rows in the input tables. Then it keeps the rows that match all the ON and WHERE conditions, and returns this as the result set. If there are multiple matching rows between the two tables, you'll get multiple rows in the result set.

If you were selecting columns from both tables, you would see that they're not actually the same row. They just have the same data from the resource table, but different data from the resource_access table. But you're not showing those latter columns in your result. Using DISTINCT merges all these rows in the result.

edited Jun 29 '14 at 22:54

answered Jun 29 '14 at 22:45

Barmar

741,623
53
500
612

Yes, I said why in my comment above. – Barmar Jun 29 '14 at 22:45
1

Suppose you also returned data from `resource_access` in your `SELECT` clause. Then you would see that the rows aren't duplicates, because they are different in these other columns. You're just not returning those columns, so you don't see that they're different rows. – Barmar Jun 29 '14 at 22:51

Gordon Linoff · Answer 2 · 2014-06-29T22:56:39.857

1

Because you are only selecting from the resource table, I would suggest putting the conditions in the where clause rather than using an explicit join:

SELECT r.id, r.name, r.group 
FROM `resource` r
WHERE r.owner_id='24' or
      EXISTS (select 1
              from resource_access ra
              where ra.resource_id = r.id and
                    ra.user_id = '24' 
             );

With this logic, the "join" cannot product duplicates.

edited Jun 29 '14 at 22:56

answered Jun 29 '14 at 22:48

Gordon Linoff

1,242,037
58
646
786

You have an extra `ON` line. – Barmar Jun 29 '14 at 22:55

score 0 · Answer 3 · answered Jun 29 '14 at 23:19

Select the ownership of resources then union it to resources with access. Resulting user_id column that is different from your WHERE RA.user_id value just means that resource was shared to them instead of them owning the resource. Hope this helps.

SELECT resource.name,resource.group,resource.owner_id AS user_id
  FROM resource
  WHERE resource.owner_id = '11'

UNION

SELECT R.name,R.group,R.owner_id AS user_id
  FROM resource_access RA 
  LEFT JOIN resource R 
     ON (R.id=RA.resource_id)
  WHERE RA.user_id = '11';

MySQL Inner Join Returning Multiples of the Same Row

3 Answers3