[thelist] (Probably) Simple SQL Stumper.

>It's a case of where I have a list of properties returned, that there
>is one sample image per return. They can choose a link to see the
>full prop and all of it's images. So, yes. One image only, even
>though the house may have more. And I don't really care which image
>is returned in the list. I had half figured that that the first one
>to show up would do.

aha, well, that's different then

you have multiple images per property?  that makes sense, but it wasn't
obvious from your first description

okay, if all you want is one, this is a bit trickier, but it can be done,
and since you don't care which one it is, there are several ways you can do
it

basically you now have to do a subselect on the images -- so that if a
property has multiple image records associated with it, you get the image
that meets the subselect condition

assuming the image records have their own ID field, you can use the lowest
(earliest, if it's an autonumber) or highest (latest)

another option would be to use the lowest or highest ImgLrg field, but this
is not as good, because you're choosing it based on a gif name, which is
iffy

so let's go with the latest Image ID

   <cfquery name="GetListAndImage" datasource="#datasource#">
     SELECT A.City, A.PropertyType, A.ExteriorType
          , A.MLSNumber, A.ArchitectureType, A.QtyRooms
          , A.Price
          , iif(isnull(I.ImgLrg),'',I.ImgLrg) as ImgLrg
       FROM Address A
    LEFT JOIN Image I
         ON A.ID = I.AddrJoin
      WHERE 1=1
   <cfif len(Form.City)>
        AND A.City = '#Form.City#'
   </cfif>
        AND I.ID = (SELECT MAX(ID)
                              FROM Image
                           WHERE AddrJoin = A.ID)
   </cfquery>

the correlation variable A in the subselect is very important

the subselect may *not* work (haven't tested this, but you should) when the
property has no images, in that case it should resolve to

     AND null=null

which, as you may know, is false (nulls are never equal to anything, even
other nulls)

i guess you'd better make sure all your properties have an image  ;o)

>> WHERE 1=1 is just me being me, i prefer this because it's more
>> obvious what it's for than ID > 0
>
> That's another surprise. I had thought that a record had to be
> returned, as opposed to a true.

nope, a condition in the WHERE clause just has to evaluate, is all

(i mailed you this one offlist last week)

1=1 evaluates to true in every database i've used it in     ;o)

carry on, and don't give up, sql is weird but it makes sense when you get
used to it

_____________
rudy limeback
http://r937.com/
http://evolt.org/