[thelist] SQL Help Please
rudy
r937 at interlog.com
Mon May 27 23:05:00 CDT 2002
joshua, i wanted to send this offlist (i fear we may be boring folks here
with sql minutiae) but your waetech id is bouncing again
>> 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
>
> Actually, I wanted the row even if it was 0
you wanted which row? there's a 0 row in table2?
because if there's no 0 row in table2, then the 1st subquery
in the 3-union query that i gave you actually *does* return
the unmatched row (i.e. similar results as an outer join) --
select stuff
from table1
where intfield1 is null and intfield2 is null
this is the same as the coalesce returning 0
okay, yes, let's stick a null into the select list where the table2 id
would've gone...
also, because of the way coalesce evaluates, the 3rd of the three
subqueries should not have tested for intfield2 (this allows for the case
where they are both not null)
so, inserting the table2 fields into the select lists, we have
select stuff, null
from table1
where intfield1 is null and intfield2 is null
union all
select stuff, table2.id
from table1, table2
where intfield1 is null and intfield2 = table2.id
union all
select stuff, table2.id
from table1, table2
where intfield1 = table2.id
this *is* the complex outer join you wanted
> I was looking for a more generalized case
> where one value was calculated and the join
> had to happen on the value as
> well as return that value within the query.
not sure i understand, because you *can* do just that
e.g. select stuff, expression
from foo left join bar
on foo.qux = expression
nothing wrong with that, the ON clause does allow complexity
if it's sql-92 that you want to avoid, the actual outer join syntax, then
you have to fake it with a union like i did -- this is the way outer joins
were accomplished before the JOIN syntax was supported
rudy
More information about the thelist
mailing list