[MYSQL] query probleem

[vorlox]

Beste Tweakers,

Ik heb een vraag over een query maar heb geen idee of dit eigenlijk wel mogelijk is.
Er zijn 5 tabellen n.l rollen, users, user rollen, items en item_rollen.

code:

roles
    roleID
    rolename

user
    userID
    username

user_role
    roleID
    userID

item
    itemID
    itemname

item_role
    roleID
    itemID

wat ik nu wil is met 1 query een dropdown vullen waarbij dus de ingelogde gebruiker allen de items te zien krijgt waarvoor deze een rol heeft.
Natuurlijk kan ik wel doorheen loopen en het dan afvangen, maar ik zou het in 1 query willen

Werkelijk geen idee hoe ik dit moet aanpakken, want je wilt dus per item gaan kijken welke rollen deze heeft in de item_role tabel en dan ook nog kijken of deze rol aan de user vast zit.

Kan iemand me een beetje op weg helpen

[Noork]

De tabellen user_role en item_role zijn volgens mij overbodig. Dit kan toch gewoon een foreign id worden naar de roles tabel? Dat maakt de query in elk geval een heel stuk makkelijker.

zoiets misschien? "select * from items, users where items.role = users.role"

Geen zin om m'n sql boekje op te zoeken, zal niet helemaal goed geformuleerd zijn.

[ Voor 34% gewijzigd door Noork op 29-01-2006 22:45 ]

[Verwijderd]

SQL:

SELECT
   item.itemID,
   item.itemname
FROM
   item
   INNER JOIN item_role ON(
      item.itemID = item_role.itemID
   )
   INNER JOIN user_role ON(
      item_role.roleID = user_role.roleID
   )
WHERE
   user_role.userID = 1

[ Voor 4% gewijzigd door Verwijderd op 29-01-2006 22:49 . Reden: typo ]

[vorlox]

Haha noork, dankje wel voor je reactie maar dat zou juist te makkelijk zijn.
De manier die je voorstelt heeft als probleem dat 1 item en 1 user tegelijk 1 rol kunnen hebben, maar dat is niet het geval.
De twee tabllen item_role en user_role hebben als doel een 1:n relatie te kunnen maken.
m.a.w een user kan 1 of meer rollen hebben en een item ook. Dat is nu juist ook mijn probleem.

[EdwinG]

Foreign key gaat niet werken als een gebruiker meerdere rollen kan hebben.

Iets in de richting van: "SELECT r.rolename FROM roles AS r INNER JOIN (user_role AS ur) ON (r.roleID = ur.roleID) WHERE ur.userID = 'id_van_user';"

edit:
Oeps, van user door naar items, stapje extra. Een mogelijke oplossing is al gepost door cheatah

[ Voor 23% gewijzigd door EdwinG op 29-01-2006 22:53 ]

[EdwinG]

vorlox schreef op zondag 29 januari 2006 @ 22:47:
De twee tabllen item_role en user_role hebben als doel een 1:n relatie te kunnen maken.

Een tussen tabel zoals user_role en item_role is pas nodig bij een n:m relatie, bij een 1:n relatie kun je de foreign key onderbrengen aan de n-kant.

Als een user meerdere rollen kan hebben, maar een rol slechts bij 1 user kan horen, kun je de foreignkey 'userID' in de rollentabel plaatsen. Dat zou de query een stuk eenvoudiger maken.

Als een user meerdere rollen kan hebben, en een rol tegelijkertijd tot meerdere users kan behoren, is de tussen tabel wel nodig.

[vorlox]

EdwinG
mybad: ik bedoelde dus geen 1:n relatie..zucht ben al te lang bezig

[vorlox]

Cheatah & EdwinG

thanks

Dit lukt al veel beter.. twee inner joins lijken nu de truuk te leveren.
Nu heb ik nog een probleem dat wanneer een item geen rol bevat deze eigenlijk ook zichtbaar moet zijn, maar dat klopt ook eigenlijk niet. Dus dit zou ik dan weer kunnen oplossen met een algemene rol voor de public items.

Erg grappig. super bedankt weer, ik kan weer verder knutselen.

[EdwinG]

Dat zou iets in de richting van
SELECT item.itemnaam WHERE item.itemID NOT IN SELECT item_role.itemID FROM item_role;
moeten worden.

[ Voor 6% gewijzigd door EdwinG op 30-01-2006 00:28 ]

[NMe]

vorlox schreef op zondag 29 januari 2006 @ 23:01:
Nu heb ik nog een probleem dat wanneer een item geen rol bevat deze eigenlijk ook zichtbaar moet zijn, maar dat klopt ook eigenlijk niet. Dus dit zou ik dan weer kunnen oplossen met een algemene rol voor de public items.

(Left) outer joins.