SQL join but limit to 1 link??

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

Thanks

andy wrote:
> 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
>
> Thanks

Do a search on this group for "strawberry query"

On 7 Mar, 23:11, "andy" <jenki...@gmail.com> wrote:
> 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
>
> Thanks

Hmm, I thought I posted to this last night!
Oh well, seciond attempt:
Search this group for "strawberry query"