Matias
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
Non ansi join syntax? group by causes a table scan?
try
select * from articles
where exists (
select 1
from article_photos
where
article_photos.photo_article_id = articles.article_id and
article_photos.priority = 1
)
Just a few pointers
1/ Drop the underscores ArticleID is just as readable as article_id
2/ foreign key fields are self documenting if you call them the Same
name ie. article_photos.articleid -> article.articleid
HTH
Neven
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]
