Hi,

I know that it works, but I'm wondering if the SQL below is valid (or
accepted) as an industry standard.

I'm so used to joins (but can't see how this can be done with joins) that
I'm nervous about using this approach.

select P.ID ProductID, V.ID VarietyID, G.ID GradeID
from Grade G, Variety V, Product P
where
((V.ProductID = P.ID) or (V.ProductID Is Null)) and
((G.VarietyID = V.ID) or (G.Varietyid IS Null)) and
((G.ProductID = P.ID) or (G.ProductID is null))

Thanks & Regards

Adam.

Adam,

It's valid but it's outdated syntax (SQL-89) and EDB will not optimize
this type of joins. You should try to rewrite your queries using the new
JOIN syntax.
Also, if I understood well you are using the NULL value to indicate that
a row is related to all rows of the "parent" table, isn't it? If it is
so, I must say that this is a design I don't recommend as it can cause
many problems with joins and RI.

--
Fernando Dias
[Team Elevate]

Hi Fernando,

Thanks for your reply...

> It's valid but it's outdated syntax (SQL-89) and EDB will not optimize
> this type of joins. You should try to rewrite your queries using the new
> JOIN syntax.
> Also, if I understood well you are using the NULL value to indicate that a
> row is related to all rows of the "parent" table, isn't it? If it is so, I
> must say that this is a design I don't recommend as it can cause many
> problems with joins and RI.

Is it possible to rewrite this with JOIN syntax (considering that some
records may relate to all rows of the parent table - you understood
correctly ?

Best Regards

Adam.

Adam,

> Is it possible to rewrite this with JOIN syntax (considering that some
> records may relate to all rows of the parent table - you understood
> correctly ?

SELECT
P.ID ProductID, V.ID VarietyID, G.ID GradeID
FROM
Product P
inner join Variety V on (V.ProductID = P.ID) or (V.ProductID Is Null)
inner join Grade G on ((G.VarietyID = V.ID) or (G.VarietyID Is Null))
                   and ((G.ProductID = P.ID) or (G.ProductID Is Null))


--
Fernando Dias
[Team Elevate]