Professional Web Applications Themes

SQL join but limit to 1 link?? - MySQL

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 ...

  1. #1

    Default 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 Guest

  2. #2

    Default Re: SQL join but limit to 1 link??

    andy wrote: 

    Do a search on this group for "strawberry query"


    Paul Guest

  3. #3

    Default Re: SQL join but limit to 1 link??

    On 7 Mar, 23:11, "andy" <com> wrote: 

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

    Captain Guest

Similar Threads

  1. left join limit 1
    By in forum MySQL
    Replies: 7
    Last Post: March 27th, 04:38 AM
  2. Replies: 2
    Last Post: September 18th, 09:59 PM
  3. outer join, cross join, union? DDL/sample data incl.
    By Kevin in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: August 5th, 08:01 PM
  4. Rewrite Query using Inner Join, Left Join e.t.c
    By Vishal in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: July 23rd, 08:02 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139