2009. július 10., péntek

MySQL (kapcsoló)táblában egy oszlopra két feltétel vizsgálata több változóval - 1:N kapcsolatnál N-re tiltás és kötelezés

Lehet, hogy a cím nem elég találó ezért leírom pontosan mire jó az alábbi (nekem és egyik ismerősömnek is több időt igénybe vevő) lekérdezés:

A feladat az volt hogy egy lekérdezésben tudjunk szűrni arra hogy mik legyenek kötelezőek, és mik legyenek tiltva egy 1:N-es típusú kapcsolótábla esetén. N-re akarunk kötelezni és tiltani is.

Konkrétabban legyen a példában egy tags_videos kapcsoló táblánk, amiben a videok ID-jához a tagek ID-jait lehet rendelni (video_id, tag_id).

A cél hogy olyan videók halmazát kapjuk meg amelyeknél tagek-között ott van például hogy 'f1','forma1','formula1' viszont nem szeretnénk a baleseteket megtekinteni ezért a 'crash','baleset','karambol' tag-eket ki szeretnénk szűrni:

Tegyük fel hogy a kötelező tag-ek ID-jai 1, 2 és 3 míg a tiltott tagek ID-jai 97,98 és 99. Az hogy ezek honnan jutnak el hozzánk most lényegtelen, azt szeretném megmutatni hogy egy lekérdezéssel a két 1:N feltétel hogyan hozható közös nevezőre:

A végeredény íme:

SELECT KOTELEZO.video_id
FROM
(SELECT video_id FROM tags_videos WHERE tag_id IN ( 97,98,99 ) GROUP BY video_id) AS TILTOTT
RIGHT OUTER JOIN
(SELECT COUNT(*) count, video_id FROM tags_videos WHERE tag_id IN (1,2,3) GROUP BY video_id) AS KOTELEZO
ON
KOTELEZO.video_id = TILTOTT.video_id
WHERE
KOTELEZO.count = 1 AND TILTOTT.video_id IS NULL
ORDER BY 1

A RIGHT OUTER JOIN "trükk" volt az ami hosszabb idő kellett hogy rájöjjünk, hogyan lesz pontosan az eredményhalmazok segítségére. Miután a NOT IN()-es subselectes verzió megvolt*, tudtuk hogy közel a megoldás... de ugye a subselecteket IN()-ben nagyon nem szeretjük :)

*ha nagyon szeretnéd látni a "félkész verziót" íme (A fentebb használt kötelező/nemkötelező ID-kkal):

SELECT KOTELEZO .video_id
FROM
(SELECT COUNT(*) count, video_id FROM tags_videos WHERE tag_id IN ( 1,2,3 ) GROUP BY video_id) AS
KOTELEZO
WHERE
KOTELEZO.count = 1
AND KOTELEZO.video_id NOT IN (SELECT video_id FROM tags_videos WHERE tag_id IN ( 97,98,99 ) GROUP BY video_id)


Nincsenek megjegyzések:

Megjegyzés küldése