[SQL] Join via subquery op derde tabel

[Eelke Spaak]

Ik ben een ASP webapplicatie aan het ontwikkelen die gebruik maakt van een Microsoft SQL Server database.

Mijn vraag gaat over de volgende drie tabellen (versimpeld; slechts relevante gegevens weergegeven:

users
userId
lastName
gender
emailAddress
isAdmin

portals
portalId
accessRightId

users_rights
rightId
userId

Het veld accessRightId in de tabel portals verwijst naar een rightId in de tabel rights (deze tabel is niet nodig voor de query). Dit rightId wordt in de koppeltabel users_rights gekoppeld aan één of meerdere users. Wanneer een user over het right beschikt dat wordt gespecificeerd door accessRightId in tabel portals krijgt hij of zij toegang tot deze portal. Admins hebben het veld isAdmin in tabel users op '1' staan, en hebben automatisch toegang tot alle portals. Voor admins zijn dus geen koppelingen opgenomen aan rights in de tabel users_rights.

Ik wil nu een recordset hebben met lastName, emailAddress, gender van alle users die óf toegangsrechten hebben tot een bepaalde portal, óf admin zijn. Ik krijg alleen niet de goede query daarvoor bedacht.

De volgende query levert me wel iedereen die expliciete toegangsrechten hebben tot de portal, maar dan zonder de admins:

code:

SELECT DISTINCT lastName, emailAddress, gender
FROM muiderburght_users_rights AS r, muiderburght_users AS u
WHERE u.userId = r.userId
AND rightId IN
   (SELECT accessRightId FROM muiderburght_portals WHERE portalId = X)

Als ik nu het volgende probeer, krijg ik nog steeds niet de admins erbij:

code:

SELECT DISTINCT lastName, emailAddress, gender
FROM muiderburght_users_rights AS r, muiderburght_users AS u
WHERE u.userId = r.userId
AND (rightId IN
   (SELECT accessRightId FROM muiderburght_portals WHERE portalId = X)
 OR u.isAdmin = 1)

Waar zit het probleem?

Alvast bedankt .

[ Voor 3% gewijzigd door Eelke Spaak op 24-05-2004 20:57 . Reden: anti layoutverneuking ]

[Verwijderd]

Je hoeft hier niet met subqueries te werken. Als je de tabel portals koppelt met een right outter join (als ik me niet vergis) en daarna in de 'where' clausule opgeeft:

code:

where
  ((portals.portalId=6) OR (users.isAdmin=1))

dan ben je er al..

[whoami]

Waarom werk je met een subquery?

Je kan toch gewoon met joins werken?

code:

SELECT lastname
FROM users u
LEFT JOIN users_rights ur ON u.userId = ur.userId
LEFT JOIN  portals p ON p.accessRightId = ur.rightId
WHERE u.isAdmin = 1 OR p.portalId IS NOT NULL
AND p.portalId =  6

't Is uit de losse pols, maar met zoiets zou je er ongeveer moeten raken. Ik ken je datamodel verder natuurlijk niet zo goed als jij dat doet, maar you should catch the drift.

[ Voor 26% gewijzigd door whoami op 24-05-2004 21:01 ]

[Verwijderd]

whoami schreef op 24 mei 2004 @ 21:00:
Waarom werk je met een subquery?

Je kan toch gewoon met joins werken?

code:

SELECT lastname
FROM users u
LEFT JOIN users_rights ur ON u.userId = ur.userId
LEFT JOIN  portals p ON p.accessRightId = ur.rightId
WHERE u.isAdmin = 1 OR p.portalId IS NOT NULL
AND p.portalId =  6

Ik denk dat die admin users geen record hebben in de user_rights ofzo, vandaar dat ik een right outter join voorstel.. met left joins zouden admins in die situatie idd niet tevoorschijn komen.. ik denk dat ik het fout zeg mbt. de tabel portals, hij moet een right join gebruiken voor de user_rights tabel geloof ik.

Dan wordt het dus:

code:

SELECT lastname
FROM users u
RIGHT JOIN users_rights ur ON u.userId = ur.userId
LEFT JOIN  portals p ON p.accessRightId = ur.rightId
WHERE u.isAdmin = 1 OR p.portalId IS NOT NULL
AND p.portalId =  6

[ Voor 16% gewijzigd door Verwijderd op 24-05-2004 21:04 ]

[whoami]

Dat hangt ervan af welke tabel in je FROM clause staat.
Als admin users geen record hebben in user_rights, en de users tabel staat in je FROM clause, dan gebruik je een LEFT OUTER JOIN.

[Eelke Spaak]

whoami schreef op 24 mei 2004 @ 21:00:
Waarom werk je met een subquery?

Je kan toch gewoon met joins werken?

code:

SELECT lastname
FROM users u
LEFT JOIN users_rights ur ON u.userId = ur.userId
LEFT JOIN  portals p ON p.accessRightId = ur.rightId
WHERE u.isAdmin = 1 OR p.portalId IS NOT NULL
AND p.portalId =  6

't Is uit de losse pols, maar met zoiets zou je er ongeveer moeten raken. Ik ken je datamodel verder natuurlijk niet zo goed als jij dat doet, maar you should catch the drift.

Dit werkt, letterlijk zoals jij postte!

De reden dat ik niet aan 'simpele' joins dacht is dat ik bij een andere query wel zo'n subquery nodig had (waar het in ieder geval logische was zoiets te gebruiken) en toen ik deze info uit de DB nodighad ging ik uit van de query die ik al eerder had geschreven. Erg bedankt!

Edit: Oja, er moet wel DISTINCT bij, maar da's een kleinigheidje.

[ Voor 10% gewijzigd door Eelke Spaak op 24-05-2004 21:06 ]

[Verwijderd]

whoami schreef op 24 mei 2004 @ 21:04:
Dat hangt ervan af welke tabel in je FROM clause staat.
Als admin users geen record hebben in user_rights, en de users tabel staat in je FROM clause, dan gebruik je een LEFT OUTER JOIN.

ah, idd, even over de FROM heengekeken.. ik dacht dat je daar alle tabellen in gefrot had, nev mind

[curry684]

Vladimir G. schreef op 24 mei 2004 @ 21:05:
[...]

Dit werkt, letterlijk zoals jij postte!

De reden dat ik niet aan 'simpele' joins dacht is dat ik bij een andere query wel zo'n subquery nodig had (waar het in ieder geval logische was zoiets te gebruiken)

Post die andere query anders ook eens, oplossingen met joins zijn vaak minimaal 2 keer sneller dan een oplossing met subqueries (indien het met een join feitelijk mogelijk is)