Thread: LEFT JOIN on one and/or another column

Hi. I have a table with two foreign keys (1 field each), like in
id                | serialext_key_original  | integerext_key_exception | integer

They mean different things, as one refers to a typical value, and the
other one refers to an exception that applies for that tuple.

Each key references a different table, one with typical data, and one with
exception data.

I'd like to join on the original, except when exception <> NULL, but I
can't quite figure out how to do so.

Can anyone help?

Thanks you. :-)

Octavio.

-- 
Octavio Alvarez.
E-mail: alvarezp@alvarezp.ods.org.

Agradezco que sus correos sean enviados siempre a esta dirección.

On Wed, Jan 28, 2004 at 20:27:00 -0800, Octavio Alvarez <alvarezp@alvarezp.ods.org> wrote:
> 
> Hi. I have a table with two foreign keys (1 field each), like in
> 
>  id                | serial
>  ext_key_original  | integer
>  ext_key_exception | integer
> 
> They mean different things, as one refers to a typical value, and the
> other one refers to an exception that applies for that tuple.
> 
> Each key references a different table, one with typical data, and one with
> exception data.
> 
> I'd like to join on the original, except when exception <> NULL, but I
> can't quite figure out how to do so.

I am not completely sure from your description what exactly you are trying
to do, but it sounds like you can left join your table to the two foreign
key tables and then use coallesce to grab the value from the appropiate
joined table.

Yes, Bruno. Thank you very much. That's what I was looking for, but since
I hadn't used CASE nor COALESCE, I didn't know it was easier that way.

The idea is that on a column I have info about a routine task, and in
another one I have info about human-made changes to the time of that task,
like assigning a different task, or moving it to another moment, and the
query I'm trying to make should answer what tasks should be done now.

Thank you.

Bruno Wolff III said:
> On Wed, Jan 28, 2004 at 20:27:00 -0800,
>   Octavio Alvarez <alvarezp@alvarezp.ods.org> wrote:
>>
>> Hi. I have a table with two foreign keys (1 field each), like in
>>
>>  id                | serial
>>  ext_key_original  | integer
>>  ext_key_exception | integer
>>
>> I'd like to join on the original, except when exception <> NULL, but I
>> can't quite figure out how to do so.
>
> I am not completely sure from your description what exactly you are trying
> to do, but it sounds like you can left join your table to the two foreign
> key tables and then use coallesce to grab the value from the appropiate
> joined table.

-- 
Octavio Alvarez.
E-mail: alvarezp@alvarezp.ods.org.

Agradezco que sus correos sean enviados siempre a esta dirección.