Since I don't think anyone answered my question I did find a combination of queries that seemed to do the trick. In case anyone else might need to know this is what I did.. please correct me if you see anything I did wrong..
CREATE TEMPORARY TABLE tmp1 SELECT vc0.imageid 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 WHERE vc0.catid='15' AND vc1.catid='37' AND vc2.catid='52' AND vc3.catid='10' GROUP BY vc0.imageid ORDER BY vc0.count + vc1.count + vc2.count + vc3.count DESC CREATE TEMPORARY TABLE tmp2 SELECT tmp1.imageid FROM vote_count LEFT JOIN tmp1 ON vote_count.imageid = tmp1.imageid WHERE vote_count.catid = '1' OR vote_count.catid = '30' GROUP BY tmp1.imageid SELECT tmp1.imageid FROM tmp1 LEFT JOIN tmp2 ON tmp1.imageid = tmp2.imageid WHERE tmp2.imageid IS NULL DROP TABLE tmp1, tmp2 "All the best people in life seem to like LINUX." --Steve Wozniak *^*^*^* Michael McGlothlin <[EMAIL PROTECTED]> http://mlug.missouri.edu/~mogmios/projects/ On Tue, 30 Oct 2001, Michael wrote: > 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 > --------------------------------------------------------------------- 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
