SQL where the limitations

Question

Below is my conclusion, I want to display only those names that are repeated. Each name is a pilot, and the number is a type of flying bat aircraft. I want to show the names of pilots who fly on the same plane. I am using sql * plus

PIL_PILOTNAME DEP_EQUIP_NO ------------------------------ ------------ Gladchuk, Chet 1085 Gladchuk, Chet 1345 Green, Edward L. 1489 Harris, Kenneth C. 1253 Leiss, Ernst L. 1194 Long, Stuart 1081 Pasewark, William B. 1026 Pasewark, William B. 1347 Scamell, Richard 1368 

My output should only display:

 PIL_PILOTNAME DEP_EQUIP_NO ------------------------------ ------------ Gladchuk, Chet 1085 Gladchuk, Chet 1345 Pasewark, William B. 1026 Pasewark, William B. 1347 

Here is my request:

 select distinct pil_pilotname,dep_equip_no from pilots join departures on dep_pilot_id = pil_pilot_id join equip_type on eq_equip_no = dep_equip_no order by pil_pilotname; 

Answer 1

In this case, using analytics is the best option.

 select PIL_PILOTNAME, DEP_EQUIP_NO from ( 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 > 1 order by pil_pilotname; 

Answer 2

You can use the GROUP BY .

 select distinct pil_pilotname,dep_equip_no, COUNT(*) AS FlightCount from pilots join departures on dep_pilot_id = pil_pilot_id join equip_type on eq_equip_no = dep_equip_no group by pil_pilotname having 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

Answer 3

One of these two (or both) should work

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

or

 select distinct p1.pil_pilotname, p1.dep_equip_no from pilots p1 join departures d1 on d1.dep_pilot_id = p1.pil_pilot_id join equip_type e1 on e1.eq_equip_no = d1.dep_equip_no where 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; 

Answer 4

 select pil_pilotname,dep_equip_no from pilots,departures,equip_type where dep_pilot_id = pil_pilot_id and eq_equip_no = dep_equip_no group by pil_pilotname having count(pil_pilotname) >1; 

Answer 5

It looks like you are not pulling anything out of the equip_type table, in which case you may not need to join it. I assume that dep_equip_no is a foreign key for equip_type.eq_equip_no and therefore cannot contain values ​​not found in equip_type .

With that in mind, my solution would look like this:

 SELECT DISTINCT p.pil_pilotname, d.dep_equip_no FROM pilots p INNER JOIN ( SELECT dep_pilot_id FROM departures GROUP BY dep_pilot_no HAVING COUNT(DISTINCT dep_equip_no) > 1 ) s ON p.pil_pilot_id = s.dep_pilot_id INNER JOIN departures d ON p.pil_pilot_id = d.dep_pilot_id 

Basically, in addition to removing the connection before equip_type , I added only one connection to the subquery, which returns a list of dep_pilot_id values ​​that have more than one separate associated dep_equip_no .

If you think you need the equip_type table in your query, you can put the remote connection back, this should not affect the results.

Answer 6

 select pil_pilotname, dep_equip_no from pilots inner join departures d on dep_pilot_id = pil_pilot_id inner join equip_type on eq_equip_no = dep_equip_no where pil_pilotname in ( select pil_pilotname from pilots inner join departures d on dep_pilot_id = pil_pilot_id inner join equip_type on eq_equip_no = dep_equip_no group by pil_pilotname having count(*) > 1) order by pil_pilotname; 

This will give you what you need. The internal choice is where the filtering takes place.