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
