SQL query for foreign key null fields

Question

I have a relationship that has the chart:

maoObra
(
        id*
    ,   descricao
    ,   valor
    ,   ...
)

And the table:

pecas
(
        id*
    ,   nome
    ,   valor
    ,   ...
)

I have another table:

ItensServico
(
        id*
    ,   data
    ,   pecas_id
    ,   maoObra_id
    ,   ...
)

Where the attributes peca_id and maoObra_id are weak foreign keys in the table of ItensServico.

On the table ItensServico I can get a tuple that either is from the table pecas or the table maoObra and never two, that is, when in a tuple I insert the attribute peca_id, the attribute maoObra_id will be null and the reverse is also true.

I need a query that returns the tuple, but returns the attributes of a table when its foreign key field is NOT null.

Answer 1

Maybe then you can solve your problem:

SELECT      ISE.id
        ,   ISE.data
        ,   Id          = ISNULL(MO.id, PE.id)
        ,   Descricao   = (CASE WHEN MO.id IS NOT NULL THEN MO.descricao    ELSE PE.nome    END)
        ,   Valor       = (CASE WHEN MO.id IS NOT NULL THEN MO.valor        ELSE PE.valor   END)
FROM        ItensServico    ISE
LEFT JOIN   maoObra         MO  ON MO.id = ISE.maoObra_id
LEFT JOIN   pecas           PE  ON PE.id = ISE.pecas_id