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

[Richard Martell <rmartell@galdosinc.com>]

Diego Ballve wrote:
> 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
> 

Then this would definitely call for an "upgrade" to Intermediate SQL
if this were to be acceptable as a naked SQL query (vs. generated from
some filter query--is this possible?). Most--if not all--commercial and 
leading open source relational databases will handle such queries these
days, so it doesn't really pose a technical hurdle. Or the implementer
does some pre-processing to make it palatable. If the backend chokes,
just throw up an exception.

I would consider this a minor enhancement. Perhaps Diego can submit
this comment ;-)


-- 
Richard