[mysql] workaround voor bug die zich met joinen voordoet

[marty]

Ik heb twee (eigenlijk meer, maar doet er voor dit probleem niet toe) tabellen, ChatUsers en ChatRooms.
In ChatUsers heb ik een kolom RoomID zitten om bij te houden in welke RoomID de user momenteel zit.

Met deze query:

MySQL:

SELECT
    CR.RoomID, CR.RoomName, CU.UserID, IF(CU.UserID IS NOT NULL, 1, 0) AS Connected
FROM ChatRooms AS CR
    LEFT JOIN ChatUsers AS CU ON CU.RoomID=CR.RoomID AND CU.UserID=4
ORDER BY RoomName

wil ik alle Rooms weergeven en daarbij ook gelijk te weten komen of de User (in dit geval een user met id=4) in die kamer zit.

Deze query werkt perfect in 4.x versies van mysql; maar niet in 3.x versies, daar vertikt mysql het om die tabel te joinen waardoor Connected 0 is. Waarom is me een raadsel

Als ik overigens dit doe in een 3.x versie:

MySQL:

SELECT
    CR.RoomID, CR.RoomName, CU.UserID, IF(CU.UserID IS NOT NULL, 1, 0) AS Connected
FROM ChatRooms AS CR
    LEFT JOIN ChatUsers AS CU ON CU.RoomID=CR.RoomID
ORDER BY RoomName

Dan krijg ik een overzicht van alle rooms met per room welke user er in zit. En zo ook user met id=4. Hij kan 't dus wel ....

Heel irritant, want dit moet ook met 3.x versies gaan werken en nu zoek ik dus een workaround in de vorm van een andere query (en géén extra query).

Iemand enig idee hoe ik die query in een andere vorm kan gieten waardoor ik in 1 query een overzicht kan krijgen van alle Rooms met daarbij aangegeven in welke Room een user met een bepaald ID zit?
ChatRooms bestaat alleen uit die twee kolommen die je ziet staan. en ChatUsers bevat dus een kolom RoomID waarmee je 'm op ChatRooms kan joinen.

p.s. aan subqueries heb ik dus ook niets omdat het ook in 3.x versies moet werken

[NMe]

Het lijkt me dat MySQL valt over de AND in je JOIN .. ON .. clause. Probeer dit eens:

code:

SELECT
    CR.RoomID, CR.RoomName, CU.UserID, IF(CU.UserID IS NOT NULL, 1, 0) AS Connected
FROM ChatRooms AS CR
    LEFT JOIN ChatUsers AS CU ON CU.RoomID=CR.RoomID
WHERE CU.UserID=4
ORDER BY RoomName

[marty]

Dan krijg ik alleen maar de Room te zien waar die user zich in bevindt en niet ook alle overige Rooms.

[Michali]

Probeer dan eens:

SQL:

SELECT
    CR.RoomID, CR.RoomName, CU.UserID, IF(CU.UserID IS NOT NULL, 1, 0) AS Connected
FROM ChatRooms AS CR
    LEFT JOIN ChatUsers AS CU ON CU.RoomID=CR.RoomID
WHERE CU.UserID=4 OR CU.UserID IS NULL
ORDER BY RoomName

Gokje, maar dat zou mischien wel moeten werken.

[marty]

Nopes, werkt ook niet. Omdat in het geval van de overige rooms bij de UserID overal een getalletje komt te staan van de users die zijn geconnect en die is dus nooit NULL. En van al die Users geeft ie dan ook aan dat ze connected zijn.

[ACM]

marty schreef op zondag 12 juni 2005 @ 19:36:
Deze query werkt perfect in 4.x versies van mysql; maar niet in 3.x versies, daar vertikt mysql het om die tabel te joinen waardoor Connected 0 is. Waarom is me een raadsel

Doet ie de join niet en krijg je altijd een 0 uit die IF of krijg je altijd een 0 uit die IF en denk je dat de join niet werkt?
Oftewel, als je de IF weglaat en die CU.UserID uitspuugt, krijg je dan altijd NULL's of toch ook af en toe een 4?

Iemand enig idee hoe ik die query in een andere vorm kan gieten waardoor ik in 1 query een overzicht kan krijgen van alle Rooms met daarbij aangegeven in welke Room een user met een bepaald ID zit?
ChatRooms bestaat alleen uit die twee kolommen die je ziet staan. en ChatUsers bevat dus een kolom RoomID waarmee je 'm op ChatRooms kan joinen.

Je query zou in mysql 3 gewoon moeten werken, er was wel es iets met dat je IS NOT NULL niet en NOT IS NULL wel kon schrijven of juist andersom, controleer dat iig nog even. Maar je kan zoiets proberen:

MySQL:

SELECT
    CR.RoomID, CR.RoomName, (SUM(CU.UserID = 4) > 0) AS Connected
FROM ChatRooms AS CR
    LEFT JOIN ChatUsers AS CU ON CU.RoomID=CR.RoomID
GROUP BY CR.RoomID, CR.RoomName
ORDER BY RoomName

Helemaal lang geleden heb ik ook nog wel gehad dat je per se CR.RoomID = CU.RoomID moest schrijven, ipv wat je nu hebt, je kan het proberen, maar het zal wel niet meer nodig zijn.

En evt kan het nodig zijn om haakjes bij je join-conditie te zetten (ON (... AND ...) )

[ Voor 10% gewijzigd door ACM op 12-06-2005 20:19 ]

[marty]

ACM schreef op zondag 12 juni 2005 @ 20:15:
[...]

Doet ie de join niet en krijg je altijd een 0 uit die IF of krijg je altijd een 0 uit die IF en denk je dat de join niet werkt?
Oftewel, als je de IF weglaat en die CU.UserID uitspuugt, krijg je dan altijd NULL's of toch ook af en toe een 4?

Dit is de output:

code:

RoomID  RoomName    UserID  Connected
1   General NULL    0
2   Test    NULL    0

De UserID blijft dus NULL, oftewel hij joined 'm niet. Terwijl ie dat wel zou moeten doen.

Je query zou in mysql 3 gewoon moeten werken, er was wel es iets met dat je IS NOT NULL niet en NOT IS NULL wel kon schrijven of juist andersom, controleer dat iig nog even.

NOT IS NULL (en NOT ISNULL) geeft een error

Maar je kan zoiets proberen:

MySQL:

SELECT
    CR.RoomID, CR.RoomName, (SUM(CU.UserID = 4) > 0) AS Connected
FROM ChatRooms AS CR
    LEFT JOIN ChatUsers AS CU ON CU.RoomID=CR.RoomID
GROUP BY CR.RoomID, CR.RoomName
ORDER BY RoomName

Ik heb ook een tijdje met een GROUP BY zitten klooien, maar dat wilde ook maar niets opleveren. Deze doet het echter perfect. Mijn dank is groot

Helemaal lang geleden heb ik ook nog wel gehad dat je per se CR.RoomID = CU.RoomID moest schrijven, ipv wat je nu hebt, je kan het proberen, maar het zal wel niet meer nodig zijn.

En evt kan het nodig zijn om haakjes bij je join-conditie te zetten (ON (... AND ...) )

Dit hielp ook niet

(btw, het is trouwens een erg vreemde bug. ik merk nu dat de bug zich niet voordoet als een user zich in RoomID=1 bevindt )