I don't have a copy of mysql handy so haven't tested this but it looks like the order by would affect the input to the join not the output (possibly even ignored, not sure). Presumably you want to change the dislpay order off the final select.
Try changing it to SELECT article_id, photo_name, priority FROM articles INNER JOIN (SELECT * FROM article_photos GROUP BY photo_article_id) photos ON article_id = photos.photo_article_id ORDER BY photos.priority ASC Usual disclaimers Bruce On 11 February 2010 10:22, Chris <crose...@gmail.com> 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 nzphpug@googlegroups.com > To unsubscribe, send email to > nzphpug+unsubscr...@googlegroups.com > -- Bruce Clement When a co-worker said he didn't want his kids getting the H1N1 vaccine because it was too new and "they haven't tested it enough", I blurted out something like, "So you'd rather test a new and poorly understood virus on them instead?" I'm not entirely proud of fighting vague and irrational fear of the unknown by invoking vague and irrational fear of the unknown, but I think it did make an impression. Petréa Mitchell -- NZ PHP Users Group: http://groups.google.com/group/nzphpug To post, send email to nzphpug@googlegroups.com To unsubscribe, send email to nzphpug+unsubscr...@googlegroups.com
