Re: [regrep] Inner joins using Entry level SQL-1992

[Diego Ballve <diego.ballve@digital-artefacts.fi>]

Richard Martell wrote:
> Hi
> 
> 
> The question came up during the last telecon about expressing joins
> using SQL in ebRS. It appears that ebRS 6.4.1 _does_ allow (inner)
> joins, since SQLTableList can have more than one table reference. But
> this is non-normative. The preamble in 6.4 (which should be incorporated
> into a new normative subsection 6.4.1) is normative, however, and
> doesn't appear to rule out (inner) joins either. It requires the use
> of Entry-level SQL-1992 only.
> 
> This is the classic formulation of an inner join supported by
> Entry-level SQL-92:
> 
> SELECT TableA.Field1, TableB.Field2, blah, blah
> FROM TableA, TableB
> WHERE TableA.Field1=TableB.Field2
> 
> 
> However, this conformance level does _not_ encompass the following
> syntax (which is allowed in Intermediate SQL); it should produce
> the same results:
> 
> SELECT TableA.Field1, TableB.Field2, blah, blah
> FROM TableA INNER JOIN TableB
> ON TableA.Field1=TableB.Field2

Hi Richard,

What experience has shown is that we need a LEFT JOIN for some cases, 
for instance you have this kind of parameterized query:

SELECT * FROM RegistryObject ro, Name_ nm, Description d
WHERE nm.parent = ro.id AND '$name' = nm.value
AND d.parent = ro.id AND '$desc' = d.value

Now, since name and description are not mandatory, any RO that does not 
have name/desc will be cut out of that result set. Imagine that you want 
to search with $name = 'MyObject' and $desc = '%'. You won't get all 
objects named 'MyObject', but only the ones that have any description.

This would return also objects without name/desc:

SELECT * FROM RegistryObject ro
LEFT JOIN Name_ nm ON nm.parent = ro.id
LEFT JOIN Description d ON d.parent = ro.id
WHERE '$name' = nm.value AND '$desc' = d.value

Regards,
Diego