Re: 3 value logic. Why is SQL so special?

From: Chris Lim <blackcap80_at_hotmail.com>
Date: 16 Sep 2006 16:59:33 -0700
Message-ID: <1158451173.087326.199940@k70g2000cwa.googlegroups.com>

Cimode wrote:
> long time. SQL is by nature a redundant language. Additionally, your
> second example is a bad one two. At the first place, there should not
> be a second table for flights if you pretend your schema is normalized.

No I didn't mean a separate table - I meant within the same table. For example, in SQL Server the query to find the occurrences of flights with the same scheduled and actual date (if the table allowed NULLs) would be:

SELECT scheduled_date,

                ISNULL(actual_date, '1 Jan 1900') AS actual_date,
                COUNT(*) AS cnt

FROM flights
GROUP BY scheduled_date, ISNULL(actual_date, '1 Jan 19000') HAVING COUNT(*) > 1 I'm just wondering what the equivalent would be if you did not allow NULLs. Wouldn't it be something like:

SELECT scheduled_date,

                CONVERT(DATETIME, '1 Jan 1900') AS actual_date,
                COUNT(*) AS cnt

FROM flights
WHERE NOT EXISTS( SELECT *

                                  FROM flight_actuals fa
                                  WHERE fa.FlightID = f.FlightID)

GROUP BY scheduled_date
HAVING COUNT(*) > 1 UNION SELECT f.scheduled_date,

                fa.actual_date,
                COUNT(*) AS cnt

FROM flights f
INNER JOIN flight_actuals fa
  ON fa.flightID = f.flightID
GROUP BY f.scheduled_date, fa.actual_date HAVING COUNT(*) > 1 Or is there a simpler way?

Chris Received on Sat Sep 16 2006 - 18:59:33 CDT