[thelist] (Probably) Simple SQL Stumper.

hey matthew, wasabi !!

(you get those bud commercials down there?)


>> Why would it not do so for every row
>> returned by the first query?
>
>Because it doesn't work that way.

true...   true...



after you run the GetList query, #ID# will give you just the first value,
unless it's within a cfoutput or -- nice one, matthew, it's ugly but it
makes the point -- a cfloop

what i don't understand, frank, is why you don't use a join -- unless you
really *did* mean to design your queries this way --

   ~ GetList gets a result set of a number of properties

   ~ GetOneImage gets a result set of one image only !?

in that case, your challenge is to figure out which of the IDs to choose to
use in the GetOneImage query  ;o)

on the other hand, if, as i suspect, you want matching images with
properties, then a join is definitely the way to go

not only is it *way* more efficient in the database, but you also get your
results in one call to the database instead of two

remember, the inter-process or inter-server database call is often the
slowest part of the total page response time

okay, here's your query using a left outer join --

   <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>
   </cfquery>

   <cfoutput query="GetListAndImage">
    ...
   <cfif Len(ImgLrg) is not 0>
     <img src="img/#ImgLrg#">
   <cfelse>
     <img src="img/pixel.gif" >
   </cfif>
   ...
   </cfoutput>


some notes about the query --

it doesn't return ID in the select list, there's no need (unless you're
displaying it, but as a general rule of thumb, it's not a good idea to
display an autonumber primary key, it serves no purpose)

the columns are qualified by the correlation variables A and I, this is
good coding practice when joining tables, it's not only self-documenting
and easier when performing maintenance, but it's necessary in cases where
there are columns with the same name

LEFT JOIN ... ON is pretty standard, but

      iif(isnull(I.ImgLrg),'',I.ImgLrg) as ImgLrg

is ms access syntax (ordinarily i would've used the COALESCE function, but
you know ms access, it's not standard)

the point of the check for null in the query is a result of it being a left
outer join

if a row from the left table in a left outer join has no matching row from
the right table, the join will return nulls for all the columns in the
select list from the right  table

so far so good, but i find it preferable to let the database substitute
something for the null rather than try to handle a null in the cfoutput

you might still test for the condition, but i find it easier to test a
zero-length string than a null in cf (not sure why, but i've never been
able to find any cf functions that adequately handle nulls... maybe i
haven't looked hard enough)

WHERE 1=1 is just me being me, i prefer this because it's more obvious what
it's for than ID > 0

finally, a custom tip (i can't take the time to write this up as a general
tip, it's too specific to this example)...

if you change

      iif(isnull(I.ImgLrg),'',I.ImgLrg) as ImgLrg

to

      iif(isnull(I.ImgLrg),'pixel.gif',I.ImgLrg) as ImgLrg

then you can dispense with the cfif in the output altogether!


good luck frank, let me know how this goes...

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