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_at_k70g2000cwa.googlegroups.com>
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:
FROM flights
WHERE NOT EXISTS( SELECT *
HAVING COUNT(*) > 1 UNION SELECT f.scheduled_date,
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?
Date: 16 Sep 2006 16:59:33 -0700
Message-ID: <1158451173.087326.199940_at_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 Sun Sep 17 2006 - 01:59:33 CEST