[thelist] (Probably) Simple SQL Stumper.

At 11:21 PM -0400 5/23/00, rudy limeback wrote:

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

I'm at the point where I don't *design* my queries--I just get'em to 
work. In 6 more months, I'll be designing them.

>    ~ GetList gets a result set of a number of properties
>    ~ GetOneImage gets a result set of one image only !?

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.


>some notes about the query --

>it doesn't return ID in the select list, there's no need.

Yes, I use the ID to retrieve a single record, from within a URL 
(href="GetRecord?ID=##")

>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

So the first part of the dot is a variable? I had thought that it had 
to be the table name. Nice.

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

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

Huh! That's another cool one.

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

It went well. As I mentioned, there were a lot of surprises in your 
approach. I like it. By the way, you write quite well, you know that?

You know, I'm in the phase of the learning curve right now where I 
understand enough to put together some basics, and some fairly solid 
functionality, but still lack sophistication.

What I find frustrating about this particular phase of the learning 
curve are the multiple hours flipping though books, pages and 
smacking my head against the key board.

So thank you to you, and all others who take the time.
-- 

Where are we going? Why am I in this handbasket?

Frank Marion                      Loofah Communications
frank@loofahcom.com               http://www.loofahcom.com