DB query explanation
I am unable to understand how the following query brings the appropriate result please explain me this
Consider the following relation schema for an airline database.
customer(id, name, age, gender)
onFlight(id, flightNo, flightDate)
flightInfo(flightNo, fromCity, toCity, startTime, duration
. Flights (flightNo, flightDate) on which there are at least two customers
SELECT f1.flightNo, f1.flightDate
FROM onFlight as f1, onFlight as f2
WHERE f1.flightNo = f2.flightNo AND f1.flightDate=f2.flightDate AND __________________ f1.id <> f2.id
can i write this query instead o开发者_开发百科f...
select flightNo, flightDate count(id) from onFlight groupBy(id) having count(id)>1
SELECT f1.flightNo, f1.flightDate
FROM onFlight as f1, onFlight as f2
WHERE f1.flightNo = f2.flightNo
AND f1.flightDate = f2.flightDate
AND f1.id <> f2.id
This builds cross-joins (all possible combinations) of all flights for each flightNo / flightDate
pairs possible, this selects all non-matching records.
This is not the most efficient way, since it will return all duplicates if there are striclty more than two customers.
The more efficient way would be:
SELECT flightNo, flightDate
FROM onFlight
GROUP BY
flightNo, flightDate
HAVING COUNT(*) >= 2
You can replace 2
with any other number here.
It could be more effective:
SELECT f1.flightNo, f1.flightDate, COUNT(*)
FROM onFlight AS f1, onFlight AS f2
WHERE f1.flightNo = f2.flightNo AND f1.flightDate=f2.flightDate
GROUP BY f1.flightNo, f1.flightDate
HAVING COUNT(*)>=2
精彩评论