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