[MySql] van twee mtm-tabellen en een subquery

[Rekcor]

Ik krijg mijn query niet aan de praat. Wat is de situatie?

Er zijn 3 tabellen:

• berichten
• voorMedewerker
• gelezenDoorMedewerker

berichten	voorMedewerker	gelezenDoorMedewerker
berichtId	berichtId	berichtId
tekst	medewerkerId	medewerkerId

Ik wil nu selecteren: alle berichten voor een bepaalde medewerker die hij/zij nog niet gelezen heeft.

SQL:

SELECT berichten.*
  FROM berichten,
       voorMedewerker
 WHERE berichten.berichtId = voorMedewerker.berichtId
   AND voorMedewerker.medewerkerId = '5'

Geeft alle berichten voor medewerker 5. Het lukt me echter niet om voor elkaar te boksen dat hij niet de berichten selecteert die betreffende gebruiker al gelezen heeft. De subquery in

SQL:

SELECT berichten.*
  FROM berichten,
       voorMedewerker
 WHERE berichten.berichtId = voorMedewerker.berichtId
   AND voorMedewerker.medewerkerId = '5'
   AND voorMedewerker.medewerkerId != ANY (SELECT gelezenDoorMedewerker.medewerkerId
                                             FROM gelezenDoorMedewerker
                                            WHERE gelezenDoorMedewerker.medewerkerId = '5')

is namelijk of altijd waar, of altijd onwaar, maar niet gekoppeld aan het berichtId.

Hoe dit op te lossen?

[Noork]

Kun je het sowieso niet oplossen met slechts 2 tabellen. Je zou ook een statuskolom kunnen toevoegen in je voorMedewerker tabel. Of kunnen medewerkers aan wie het bericht niet is geadresseerd ook het bericht lezen?

[GlowMouse]

Geen wonder dat die subquery altijd of waar of altijd onwaar is, je laat hem niet afhangen van berichtId. Je lijkt dat zelf ook door te hebben, maar waarom pas je de WHERE in je subquery dan niet aan?

[ Voor 31% gewijzigd door GlowMouse op 05-09-2008 12:31 ]

[Rekcor]

GlowMouse schreef op vrijdag 05 september 2008 @ 12:30:
Geen wonder dat die subquery altijd of waar of altijd onwaar is, je laat hem niet afhangen van berichtId. Je lijkt dat zelf ook door te hebben, maar waarom pas je de WHERE in je subquery dan niet aan?

Ok, maar dan is mijn kennis over subqueries kennelijk beperkt/onjuist, want ik leef(de) in de veronderstelling dat deze geheel onafhankelijk van de 'main' query draaien, m.a.w. dat de twee A's in deze query:

SQL:

SELECT * FROM A WHERE id IN (SELECT id FROM B,A WHERE B.id=A.id)

geheel onafhankelijk/verschillende instanties zijn (stel het waren objecten).

[GlowMouse]

Dan moet je A niet in je subquery definieren, dan komt hij wel overeen.

SQL:

SELECT * FROM A WHERE id IN (SELECT id FROM B WHERE B.id=A.id)

Verder heeft Noork wel gelijk: waarom zou je dit zover normaliseren? Queries worden complexer en het kost nog meer opslagruimte ook (zelfs als maar een kwart van de berichten gelezen worden).

[Rekcor]

Mmm....

SQL:

SELECT berichten.*
  FROM berichten,
       voorMedewerker
 WHERE berichten.berichtId = voorMedewerker.berichtId
   AND voorMedewerker.medewerkerId = '5'
   AND voorMedewerker.medewerkerId NOT IN (SELECT gelezenDoorMedewerker.medewerkerId
                                             FROM gelezenDoorMedewerker
                                            WHERE gelezenDoorMedewerker.medewerkerId = '5'
                                              AND gelezenDoorMedewerker.berichtId = berichten.berichtId)

werkt ook niet .

[Rekcor]

GlowMouse schreef op vrijdag 05 september 2008 @ 12:49:
Verder heeft Noork wel gelijk: waarom zou je dit zover normaliseren? Queries worden complexer en het kost nog meer opslagruimte ook (zelfs als maar een kwart van de berichten gelezen worden).

Ik maak gebruik van een framework waarmee je eenvoudig HTML formulieren kunt genereren op basis van m2m tabellen. Voorwaarde is echter wel dat deze m2m tabellen twee kolommen hebben en niet meer.

(jullie hebben dus helemaal gelijk, maar ik moet dit draaiende krijgen in de gegeven situatie)

[ Voor 8% gewijzigd door Rekcor op 05-09-2008 12:54 ]

[Noork]

bedoel je sowieso niet "AND voorMedewerkerberichten.berichtID not in(select berichtID from gelezendoormedewerker where mederwerkerID = 5)"

[ Voor 7% gewijzigd door Noork op 05-09-2008 12:55 ]

[GlowMouse]

Rekcor schreef op vrijdag 05 september 2008 @ 12:52:
Mmm....
werkt ook niet .

En waarom werkt dat niet? En indien je de verkeerde rijen terugkrijgt: heb je wat CREATE TABLE en INSERT-code waarmee we dit kunnen reproduceren?

_{en in plaats van 2x achter elkaar een post plaatsen, kun je beter de edit-knop gebruiken}

[ Voor 6% gewijzigd door GlowMouse op 05-09-2008 13:07 ]

[Rekcor]

GlowMouse schreef op vrijdag 05 september 2008 @ 13:06:
[...]

En waarom werkt dat niet?

Hij werkt niet als de subquery niets/null teruggeeft (kom ik zojuist achter). Als ik twee berichten invoer, waarvan 1 ongelezen en 1 gelezen, doet hij het prima. Zet ik het gelezen bericht echter op ongelezen, dan geeft hij niets terug. Dit schijnt zo te zijn vanwege de SQL standard:

http://dev.mysql.com/doc/...perators.html#function_in
To comply with the SQL standard, IN returns NULL not only if the expression on the left hand side is NULL, but also if no match is found in the list and one of the expressions in the list is NULL.

Leuk natuurlijk, maar ik zit met de gebakken peren...

(Een INSERT/CREATE geven kan wel, maar wordt een beetje ingewikkeld omdat de tabellen in werkelijkheid veel groter zijn.)

[ Voor 6% gewijzigd door Rekcor op 05-09-2008 13:29 . Reden: toevoeging + verbetering ]

[GlowMouse]

Rekcor schreef op vrijdag 05 september 2008 @ 13:18:
Zet ik het ongelezen bericht echter op gelezen, dan geeft hij niets terug.

Klopt toch, je wilt alleen ongelezen berichten en die zijn er dan niet.

Een INSERT/CREATE geven kan wel, maar wordt een beetje ingewikkeld omdat de tabellen in werkelijkheid veel groter zijn.

Langer bedoel je? Haal je alles toch weg wat niet nodig is voor reproductie.

[Rekcor]

GlowMouse schreef op vrijdag 05 september 2008 @ 13:26:
[...]

Klopt toch, je wilt alleen ongelezen berichten en die zijn er dan niet.

Sorry, gelezen moet ongelezen zijn en vice versa (heb het nu aangepast in mijn post). Abstracter gesteld:

SQL:

SELECT * FROM A WHERE A.columnName NOT IN (NULL)

Geeft bij mij (MySQL 5) altijd 0 rijen terug, terwijl je zou verwachten dat hij juist alle rijen teruggeeft.

[GlowMouse]

NULL en een lege resultset zijn verschillende dingen. SELECT veld FROM a WHERE a.veld NOT IN(SELECT veld FROM a WHERE 1=2) geeft gewoon alle rijen terug (MySQL 5.0.41).

[Rekcor]

GlowMouse schreef op vrijdag 05 september 2008 @ 13:34:
NULL en een lege resultset zijn verschillende dingen. SELECT veld FROM a WHERE a.veld NOT IN(SELECT veld FROM a WHERE 1=2) geeft gewoon alle rijen terug (MySQL 5.0.41).

Mmm... je hebt gelijk, maar mijn query geeft alleen rijen terug als de subquery >0 resultaten geeft.

Ik ga maar eens een workaround verzinnen denk ik.

[ Voor 6% gewijzigd door Rekcor op 06-09-2008 07:45 ]

[ACM]

Rekcor schreef op vrijdag 05 september 2008 @ 12:52:
werkt ook niet .

Je verwerkt hier een soort not exists in een not in... dat gaat niet zo goed werken nee.

Er zijn vziw drie klassieke aanpakken voor dit soort problemen:

De "hij mag niet in dit lijstje voorkomen"-aanpak:

SQL:

SELECT *
FROM berichten b
WHERE b.medewerkerId = 5
AND b.berichtId NOT IN ( SELECT g.berichtId 
    FROM gelezenDoorMedewerker g
    WHERE g.medewerkerId = b.medewerkerId)

De "er mag geen bevestiging van zijn"-aanpak:

SQL:

SELECT *
FROM berichten b
WHERE b.medewerkerId = 5
AND NOT EXISTS ( SELECT * 
    FROM gelezenDoorMedewerker g
    WHERE g.medewerkerId = b.medewerkerId AND g.berichtId = b.berichtId)

En een variant op de "er mag bevestiging van zijn"-aanpak, die nog meer een "er mag niet wel een bevestiging zijn"-aanpak is dan die hierboven. Deze is populair voor MySQL omdat dit de enige werkbare variant was van toen er nog geen subqueries waren, en is performance-technisch vaak nog steeds beter. Dat verschil neemt wel af en verdwijnt helemaal zodra je meer complexe dingen met de buitenste select wilt doen.

SQL:

SELECT *
FROM berichten b
  LEFT JOIN gelezenDoorMedewerker g
           ON g.medewerkerId = b.medewerkerId AND g.berichtId = b.berichtId
WHERE b.medewerkerId = 5
AND g.medewerkerId IS NULL -- hier maakt het niet uit welk veld je checked
-- zolang het maar een not null veld is