[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