SQL where restrictions

In this case the best option would be to use analytics

select PIL_PILOTNAME, DEP_EQUIP_NOfrom (        select pil_pilotname,dep_equip_no, count(*) over (partition by pil_pilot_id) as cnt        from pilots            join departures on (dep_pilot_id = pil_pilot_id)            join equip_type on (eq_equip_no = dep_equip_no)    )where cnt > 1order by pil_pilotname;

One of these two (or both) should work

select distinct pil_pilotname, dep_equip_no, count(*) as c from pilotsjoin departures on dep_pilot_id = pil_pilot_id  join equip_type on eq_equip_no = dep_equip_nogroup by pil_pilotnamewhere c > 1order by pil_pilotname;

Or

select distinct p1.pil_pilotname, p1.dep_equip_no from pilots p1join departures d1 on d1.dep_pilot_id = p1.pil_pilot_id  join equip_type e1 on e1.eq_equip_no = d1.dep_equip_nowhere exists (    select distinct p2.pil_pilotname from pilots p2    join departures d2 on d2.dep_pilot_id = p2.pil_pilot_id      join equip_type e2 on e2.eq_equip_no = d2.dep_equip_no    where p1.dep_equip_no != p2.dep_equip_no)order by p1.pil_pilotname;

You can make use of a GROUP BY clause.

select distinct pil_pilotname,dep_equip_no, COUNT(*) AS FlightCount from pilots  join departureson dep_pilot_id = pil_pilot_id  join equip_typeon eq_equip_no = dep_equip_nogroup by pil_pilotnamehaving FlightCount > 1;

• http://database-programmer.blogspot.com/2008/04/group-by-having-sum-avg-and-count.html
• http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html