mysql join gives me duplicate results

and glazed eyes

[jamie]

tearing my hair out time ;-)

i have 2 db tables to manage a standard news page:

table = articles
ID
title
story
date

table = pics
ID
story_ID
pic_url
alt

here is the theory:

some of the stories have several pics to go with them: in which case i would like to display one pic with a link to see more

some of them have only 1 pic: in which case i don't print a link to see more pics

some of the stories have no pics at all: in which case no pic display.

i am accessing the pics table on the
articles.ID = pics.story_ID

here is my select statement:

SELECT articles.title, articles.story, articles.date, pics.pic_url, pics.alt FROM articles LEFT JOIN pics on articles.ID = pics.story_ID WHERE articles.ID = $ID

if there is more than one pic for the story concerned, the results are returned as many times as there are pics: e.g. for 5 pics:

title, story, date, pic_url, alt
title, story, date, pic_url, alt
title, story, date, pic_url, alt
title, story, date, pic_url, alt
title, story, date, pic_url, alt

which is ridiculous duplication of title, story and date

i could of course limit 0,1 the query, but then i have no way of knowing if there are more pics to be linked to and displayed.

ideally what i would like is a way of getting just the single row of title, story and date from the articles.table AND all the associated pics?

is that understandable?

much obliged for any help

[bcc1234]

Not as a single query.

getting just the single row of title, story and date from the articles.table AND all the associated pics?

One row ¦ all pics - that cannot be logically represented in one row (well, unless you want a variable number of columns in the result tuples). That would give you a three-dimensional result.

Move it into the app. logic out of the db.

[jatar_k]

I think in this instance you either query twice or repeat data as you mentioned.

I believe the double query is the way to go.

[jamie]

i more i thought about this last night, the more i thought that the double query looked like the way to go as well.

everything i read says mysql queries are the performance bottlenecks, so i guess i was trying too hard (unlogically) to reduce the number of queries to one.

i suppose it would be possible if i included all pic_urls in one comma seperated row, but i think that defeats a bit the logic of the db structure ;-)

thanks both, much appreciated!

[vincevincevince]

yes, do it in two stages

$sqla="SELECT articles.title, articles.story, articles.date FROM articles WHERE articles.ID = $ID ";
$sqlb="SELECT pics.pic_url, pics.alt FROM pics WHERE pics.story_ID = $ID";
$articledata=mysql_fetch_row(mysql_query($sqla));
$handleb=mysql_query($sqlb);
while ($picturedata[$a++]=mysql_fetch_row($handleb);

this will give:
$articledata (contains title, story, date)
$picturedata[0] (contains url, alt for pic 0)
$picturedata[1] (contains url, alt for pic 1)
...
$picturedata[n] (contains url, alt for pic n)

[jamie]

thanks vince,

whilst learning, it is always interesting to see how other people code. i still laugh at TMTOWTDI [tmtowtdi.com] from a post by andreas ;-)