Database joins with three tables, one is a category / tag table
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
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
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
select unit.name from unit, unitcategory, unitcategoryuser where unit.id = unitcategory.unitid and unitcategory.categoryid = unitcategoryuser.categoryid and unitcategoryuser.userid = "Me"
Something like that?
精彩评论