OASIS Mailing List ArchivesView the OASIS mailing list archive below
or browse/search using MarkMail.


Help: OASIS Mailing Lists Help | MarkMail Help

regrep message

[Date Prev] | [Thread Prev] | [Thread Next] | [Date Next] -- [Date Index] | [Thread Index] | [List Home]

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

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
> 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


[Date Prev] | [Thread Prev] | [Thread Next] | [Date Next] -- [Date Index] | [Thread Index] | [List Home]