[thelist] SQL Help Please

rudy r937 at interlog.com
Mon May 27 19:45:00 CDT 2002


> Is there another way to do this without SQL-92 syntax?

yes, but it's ugly -- although perhaps not as ugly as yours
(yours is the better way of expressing the relationships)

> I realize that whenever I'm displaying the results on the
> page I could just do a simple SELECT query to grab the
> value from table2 during each iteration, but I want
> to avoid that.

woohoo!!  thank you joshua

a select inside a loop is indeed evil

well, not evil as in wrong results, evil as in poor performance

;o)


let's start at the rear of the bus and move forwards

   COALESCE(intfield1, intfield2, 0) AS fk_id

if both intfield1 and intfield2 are null, you get a 0, and then if you try
to do a left outer join to table 2, i'm guessing you won't find any rows,
so i'd say in this case, don't even other with the join

if one of them is not null, you want a left outer join to that row in
table2, but i'm guessing you *will* find a row, so in this case, it's
probably not a left outer join either


   select stuff
      from table1
    where intfield1 is null and intfield2 is null
union all
   select stuff
      from table1, table2
    where intfield1 is null and intfield2 = table2.id
union all
   select stuff
      from table1, table2
    where intfield2 is null and intfield1 = table2.id

this is, if you analyze carefully, a left outer join because of what it
returns (and not because of how it's written)

rudy




More information about the thelist mailing list