Hi,
I've got two tables, links and categories.
links ...
id int(11) NOT NULL auto_increment,
name text,
note text,
link text,
category1 int(11) NOT NULL default '0',
category2 int(11) NOT NULL default '0',
category3 int(11) NOT NULL default '0',
date_added timestamp(14) NOT NULL,
rating int(11) NOT NULL default '0',
how_many_votes int(11) NOT NULL default '0',
display enum('yes','no') NOT NULL default 'no',
PRIMARY KEY (id)
categories ...
id int(11) NOT NULL auto_increment,
name text,
description text,
display enum('yes','no') NOT NULL default 'yes',
PRIMARY KEY (id)
Any one link could belong to as many as 3 categories. Each link also has a
numerical rating. What I'm trying to do is find the highest rated link in
each category so I can display the link. From the mysql docs I've decided
to create a temporary table to figure this out but the sql below isn't
working. Does anyone see any glaring errors here? Or do you have thoughts
on how to do this easier?
CREATE TEMPORARY TABLE tmplinks (
id int(11),
name text,
link text,
category int(11) NOT NULL default '0',
rating int(11) NOT NULL default '0',
how_many_votes int(11) NOT NULL default '0');
LOCK TABLES links READ, categories READ, tmplinks WRITE;
INSERT INTO tmplinks SELECT id, name, link, MAX(category1) AS category,
rating, how_many_votes FROM links GROUP BY category1;
INSERT INTO tmplinks SELECT id, name, link, MAX(category2) AS category,
rating, how_many_votes FROM links GROUP BY category2;
INSERT INTO tmplinks SELECT id, name, link, MAX(category3) AS category,
rating, how_many_votes FROM links GROUP BY category3;
SELECT categories.id, categories.name, links.id, links.name, links.link,
links.category1, links.category2, links.category3, links.rating,
links.how_many_votes, tmplinks.category, tmplinks.rating
FROM categories, tmplinks
LEFT JOIN links ON categories.id = links.category1 OR categories.id =
links.category2 OR categories.id = links.category3
WHERE (links.category1=tmplinks.category OR
links.category2=tmplinks.category OR links.category3=tmplinks.category) AND
links.rating=tmplinks.rating AND links.display="yes" GROUP BY
categories.name;
UNLOCK TABLES;
DROP TABLE tmplinks;
Thanks much!
-Matt MacDougall
---------------------------------------------------------------------
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
