What's wrong with this query ? SELECT article_id, photo_name, priority FROM articles,article_photos photos WHERE photos.priority=1 AND article_id = photos.photo_article_id GROUP BY article_id
On 11/02/2010, at 10:22 AM, Chris wrote: Hi guys, I am using MySql 5.0.89-community I have two database tables 'articles' and 'article_photos' where each article has multiple photos and each photo has an priority field associated with it. I am trying to get a list of articles with a photo for each one that has the priority of 1 CREATE TABLE articles ( article_id int(11) unsigned NOT NULL auto_increment, article_title tinytext NOT NULL, KEY article_id (article_id) ); INSERT INTO articles VALUES (1, 'News Article 1'); CREATE TABLE article_photos ( photo_id int(11) unsigned NOT NULL auto_increment, photo_article_id int(11) unsigned NOT NULL default '0', photo_name tinytext NOT NULL, priority tinyint(4) unsigned NOT NULL default '1', KEY photo_id (photo_id) ); INSERT INTO article_photos VALUES (1, 1, 'Photo 1', 2); INSERT INTO article_photos VALUES (2, 1, 'Photo 2', 3); INSERT INTO article_photos VALUES (3, 1, 'Photo 3', 1); The query below should return the article with a photo that has the priority set to 1 but it does not. Does anyone know a workaround for this? SELECT article_id, photo_name, priority FROM articles INNER JOIN (SELECT * FROM article_photos GROUP BY photo_article_id ORDER BY priority ASC) photos ON article_id = photos.photo_article_id -- NZ PHP Users Group: http://groups.google.com/group/nzphpug To post, send email to [email protected] To unsubscribe, send email to [email protected] -- NZ PHP Users Group: http://groups.google.com/group/nzphpug To post, send email to [email protected] To unsubscribe, send email to [email protected]
