Re: Demo: Modelling Cost of Travel Paths Between Towns

"Neo" <neo55592_at_hotmail.com> wrote in message news:4b45d3ad.0411161931.12c65c47_at_posting.google.com...
> > > > UPDATE your_table SET age = age + 1
> > > > WHERE name = 'john' AND age IS NOT NULL
> > >
> > > You are not actually suggesting a solution with NULLs, are you?
> > >
> > > Per CJ Date, "NULLs and 3VL are a mistake
> > > and have no place in a clean formal system like the relational model".
> >
> > Okay, I won't use a null, I'll use the idea of "unknown" instead.
>
> An thus you have introduced 3VL into your system. If john age is
> unknown, how should the following code below proceed?
>
> if (john.age == mary.age){
> // True
> }
> else{
> // False
> }
>
> If your system evaluates it to either TRUE or FALSE, you may be wrong
> in some cases. It actually needs a third case "// Unknown".
>
> Per Date, "3VL suffers from the very serious ('showstopper') problem
> that it does not match reality - that is, results that are correct
> according to 3VL are sometimes incorrect in the real world. NULLs and
> 3VL undermine the entire foundation of the relational model."
>
> Attempts to mask NULLs with unknowns or any other value, don't work
> (you must not have read my prior posts expounding the above point).

Let's say the users have agreed to use the value 0 to indicate unknown age. If we have:

NAME AGE
John 0
Mary 0
Bill 30
Mike 30

All you need to do is include WHERE <> 0 in your query. Something like this would work:

SELECT t1.name, t2.name
FROM age_test t1, age_test t2

WHERE t1.age = t2.age
AND t1.name <> t2.name
AND t2.name <> t1.name