Database joins with three tables, one is a category / tag table

Question

I have three database tables - one containing units, one containing categories of those units, and one containing user permissions.

If a user can access a category, they are allowed to access any units within the category.

I am trying to create a query that will retrieve a list of units they are allowed to access. So far I have a very awkward query that returns duplicates, and am a bit stuck, and it's Friday afternoon, and I wanna go home!

Here are my tables:

UnitCategory

categoryid     unitid

Unit

id             name

UnitCategoryUser

unitid         userid         categoryid
               - can be null  - can be null

Thanks

Answer 1

SELECT U.Name
FROM UnitCategoryUser UCU
JOIN UnitCategory UC ON UC.categoryid = UCU.categoryid
JOIN Unit U ON U.id = UC.UnitId
WHERE UCU.UserId = YourUserId

Does that not return your correct data? I may be missing something :x

Answer 2

select unit.name from unit, unitcategory, unitcategoryuser where unit.id = unitcategory.unitid and unitcategory.categoryid = unitcategoryuser.categoryid and unitcategoryuser.userid = "Me"

Something like that?

Answer 3

I suspect that UnitCategoryUser is being used as a catch-all table for the relationships between users, categories and units - so that units are recorded as belonging to categories on records where the userid is null, and users can access units even if they don't have access to their categories, where the categoryid is null.

It would also be possible for units to belong to multiple categories, and for users to be recorded as being able to access the same units mutliple times - both where a user can access multiple categories (each of which can access the same unit) and where a user has been granted access to a unit without access to the category, then subsequently granted access to the category.

The following query should return all units for a specified user:

select distinct u.name
from unit u 
join unitcategory uc on u.id = uc.unitid
join unitcategoryuser ucu on uc.category_id = ucu.categoryid
where ucu.userid = YourUserID
union
select distinct u2.name
from unit u2
join unitcategoryuser ucu2 on u2.id = ucu2.unitid
where ucu2.userid = YourUserID