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
