CODEWITHSUNDEEP
phpmysqlleft-joininner-joinright-join
Nathan
Nathan

Reputation: 316

MySQL Inner Join Returning Multiples of the Same Row

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.

Upvotes: 3

Views: 7609

Answers (3)

no_juan
no_juan

Reputation: 67

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';

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269933

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.

Upvotes: 1

Barmar
Barmar

Reputation: 781098

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.

Upvotes: 7

Related Questions