I have a table:
CREATE TABLE vote_count (
id int(10) DEFAULT '0' NOT NULL auto_increment,
imageid int(6) DEFAULT '0' NOT NULL,
catid int(4) DEFAULT '0' NOT NULL,
count int(6) DEFAULT '0' NOT NULL,
PRIMARY KEY (id),
INDEX (imageid, catid)
);
and am trying to query for all image id's that are in an intersection of
catid's I supply and are a difference from all of the union'd catid's I
also supply..
SELECT vc0.imageid, vc4.catid, vc5.catid FROM vote_count AS vc0 LEFT JOIN
vote_count AS vc1 ON vc0.imageid = vc1.imageid LEFT JOIN vote_count AS vc2
ON vc0.imageid = vc2.imageid LEFT JOIN vote_count AS vc3 ON vc0.imageid =
vc3.imageid LEFT JOIN vote_count AS vc4 ON vc0.imageid = vc4.imageid LEFT
JOIN vote_count AS vc5 ON vc0.imageid = vc5.imageid WHERE vc0.catid='15'
AND vc1.catid='37' AND vc2.catid='52' AND vc3.catid='10' AND NOT (
vc4.catid = '30' OR vc4.catid = '1' OR vc5.catid = '1' OR vc5.catid = '30'
) GROUP BY vc0.imageid ORDER BY vc0.count + vc1.count + vc2.count +
vc3.count DESC;
This and everything else I've tried so far doesn't seem to work. The first
intersection I can make work without problem but then making a union of
the other catid's and finding the difference seems to mess up. The problem
seems to be that while I can strip individual rows from the results the
imageid still gets through because the catid is only linked once per image
per row.
I also tried this with similar poor results:
SELECT vc0.imageid, vc0.catid, vc1.catid, vc2.catid, vc3.catid,
vcneg.catid FROM vote_count AS vc0 LEFT JOIN vote_count AS vc1 ON
vc0.imageid = vc1.imageid LEFT JOIN vote_count AS vc2 ON vc0.imageid =
vc2.imageid LEFT JOIN vote_count AS vc3 ON vc0.imageid = vc3.imageid LEFT
JOIN vote_count AS vcneg ON vc0.imageid = vcneg.imageid WHERE
vc0.catid='15' AND vc1.catid='37' AND vc2.catid='52' AND vc3.catid='10'
AND ( vcneg.catid != '30' OR vcneg.catid != '1' ) GROUP BY vc0.imageid
ORDER BY vc0.count + vc1.count + vc2.count + vc3.count DESC;
Can anyone tell me how to do this the right way? Thanks.
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
