Good day! The question is this. There are two tables: one parent and one child - Asubtypeo and aevent respectively. The Asubtypeo table stores reference data, where the primary key is ID_Subtype. In the aevent table, the associated Code_subtype field is not everywhere filled with codes from ID_Subtype. Question: how to include in the selection all values, empty and filled with Code_subtype, and not just filled, when linked with the Asubtypeo table? I heard that NVL can help in this matter ...

How to add the following code below:

Select a.*, b.* from sevent a, Asubtypeo b WHERE (ID_Subtype = Code_subtype)
  • Did I understand you correctly that in the child table are stored "nobody's children"? It should not and can not be. It is necessary to change the concept along with the scheme. - BuilderC
  • You can try to link tables through one-way join (left join or right join). - Rams666

1 answer 1

You don't need nvl, you need an outer join.

In Oracle, if I remember correctly, it is done like this:

 Select a.*, b.* from sevent a, Asubtypeo b WHERE b.ID_Subtype (+) = a.Code_subtype

If I incorrectly remembered the syntax, then it is possible in a more classical way:

 Select a.*, b.* from sevent a left join Asubtypeo b on ID_Subtype = Code_subtype

A detailed manual can be found here: http://docs.oracle.com/cd/B19306_01/server.102/b14200/queries006.htm

  • Thanks, I will try). - IntegralAL