[thelist] SQL Help Please

[rudy]

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