Posted by andy on 03/08/07 00:21

Hey all,

I have the following MySQL statement:

SELECT tree.id, tree.type, tree.size, tree_pic.filename
FROM tree, tree_pic
WHERE
AND tree.id=tree_pics.treeid
AND tree.type="oak";

I want to show the details of the tree with 1 picture.

this works ok if there is 1 picture linked to 1 tree, when I have more
than 1 picture linked to 1 tree I get duplicate records with the
picture changing each time.

Say the database contains 2 tree records and 3 picture records, 1 tree
links to 1 picture and 1 tree links to 2 pictures.

I would get:

Tree Id:1 Type:Oak Size:1m Filename: tree1-pic1.jpg
Tree Id:2 Type:Oak Size:2m Filename: tree2-pic1.jpg
Tree Id:2 Type:Oak Size:2m Filename: tree2-pic2.jpg

How do I limit to only 1 picture per tree record so I would get:

Tree Id:1 Type:Oak Size:1m Filename: tree1-pic1.jpg
Tree Id:2 Type:Oak Size:2m Filename: tree2-pic1.jpg

Can this be one with 1 query?

Thanks

[Back to original message]