All geek questions in one place

Subquery to search for information in table B for the maximum value in table A

I have a huge group flightstraveling between airports.

Each airport has an ID and (x, y) coordinates.

For this list of flights owned by the user, I want to find the northernmost (highest) airport.

Here is the query that I am currently using:

SELECT name,iata,icao,apid,x,y 
  FROM airports 
 WHERE y=(SELECT MAX(y) 
            FROM airports AS a
               , flights AS f 
           WHERE (f.src_apid=a.apid OR f.dst_apid=a.apid) AND f.uid=[user_id]
         )

It works beautifully and reasonably quickly, as long as it yis unique (= there is only one airport at this latitude), but it fails as soon as it is not. Unfortunately, this happens quite often, such as. military and civilian airports have separate records, even if they occupy the same coordinates.

, , MAX(y) (a.apid) , y, . ?

, , apid '3728':

mysql> select * from flights where uid=35 and src_apid=3728 limit 1;
+------+----------+----------+----------+----------+------+------+-----------+-------+--------+------+------+------+--------+----------+--------------+--------------+---------------------+------+------------+------+
| uid  | src_apid | src_time | dst_apid | distance | code | seat | seat_type | class | reason | plid | alid | trid | fid    | duration | registration | note         | upd_time            | opp  | src_date   | mode |
+------+----------+----------+----------+----------+------+------+-----------+-------+--------+------+------+------+--------+----------+--------------+--------------+---------------------+------+------------+------+
|   35 |     3728 | NULL     |     3992 |     4116 | NW16 | 23C  | A         | Y     | L      |  167 | 3731 | NULL | 107493 | 08:00:00 |              | del. typhoon | 2008-10-04 10:40:58 | Y    | 2001-08-22 | F    | 
+------+----------+----------+----------+----------+------+------+-----------+-------+--------+------+------+------+--------+----------+--------------+--------------+---------------------+------+------------+------+

:

mysql> select * from airports where y=21.318681;
+-----------------------+----------+---------------+------+------+-------------+-----------+-----------+------+------+----------+------+
| name                  | city     | country       | iata | icao | x           | y         | elevation | apid | uid  | timezone | dst  |
+-----------------------+----------+---------------+------+------+-------------+-----------+-----------+------+------+----------+------+
| Honolulu Intl         | Honolulu | United States | HNL  | PHNL | -157.922428 | 21.318681 |        13 | 3728 | NULL |      -10 | N    | 
| Hickam Air Force Base | Honolulu | United States |      | PHIK | -157.922428 | 21.318681 |        13 | 7055 |    3 |      -10 | N    | 
+-----------------------+----------+---------------+------+------+-------------+-----------+-----------+------+------+----------+------+

, y = 21.318681, , , apid 3728 (), apid 7055 ().

+3
4

? , Y . , Y . - .

drop table airports;
drop table flights;

create table airports(
   apid    int         not null
  ,apname  varchar(50) not null
  ,x       int         not null
  ,y       int         not null
  ,primary key(apid)
  ,unique(apname)
);

create table flights(
   flight_id int         not null auto_increment
  ,src_apid  int         not null
  ,dst_apid  int         not null
  ,user_id   varchar(20) not null
  ,foreign key(src_apid) references airports(apid)
  ,foreign key(dst_apid) references airports(apid)
  ,primary key(flight_id)
  ,index(user_id)
);

insert into airports(apid, apname, x, y) values(1, 'Northpole Civilian',     50, 100);
insert into airports(apid, apname, x, y) values(2, 'Northpole Military',     50, 100);
insert into airports(apid, apname, x, y) values(3, 'Transit point',          50, 50);
insert into airports(apid, apname, x, y) values(4, 'Southpole Civilian',     50, 0);
insert into airports(apid, apname, x, y) values(5, 'Southpole Military',     50, 0);

insert into flights(src_apid, dst_apid, user_id) values(4, 3, 'Family guy');
insert into flights(src_apid, dst_apid, user_id) values(3, 1, 'Family guy');

insert into flights(src_apid, dst_apid, user_id) values(5, 3, 'Mr Bazooka');
insert into flights(src_apid, dst_apid, user_id) values(3, 2, 'Mr Bazooka');

select airports.apid
      ,airports.apname
      ,airports.x
      ,airports.y
  from (select max(a.y) as y
          from flights  f
          join airports a on (a.apid = f.src_apid or a.apid = f.dst_apid)
         where f.user_id = 'Family guy'
       ) as northmost 
  join (select a.apid
              ,a.y
          from flights  f
          join airports a on (a.apid = f.src_apid or a.apid = f.dst_apid)
         where f.user_id = 'Family guy'
       ) as userflights on(northmost.y = userflights.y)   
  join airports on(userflights.apid = airports.apid);

. ,

select airports.*
  from (select case when s.y > d.y then s.apid else d.apid end as apid
              ,case when s.y > d.y then s.y    else d.y    end as northmost
          from flights  f
          join airports s on(f.src_apid = s.apid)
          join airports d on(f.dst_apid = d.apid)
         where f.user_id = 'Family guy'
         order by northmost desc
         limit 1
       ) as user_flights
  join airports on(airports.apid = user_flights.apid);
+1

:

SELECT name,iata,icao,apid,x,y 
FROM airports AS a, flights AS f 
WHERE f.src_apid=a.apid OR f.dst_apid=a.apid
ORDER BY y DESC LIMIT 1

, .

+2

, user (userid, name)

select u.name, ap.name
     , ap.iata
     , ap.icao
     , ap.apid
     , ap.x
     , max(ap.y)  
  from users u
     , airports ap
     , flights f
 where u.userid=f.userid
   and (   f.src_apid=ap.apid 
        OR f.dst_apid=ap.apid
       )
group by u.name, ap.name,ap.iata,ap.icao,ap.apid,ap.x

, .

GROUP BY:

  • , MySQL "group by u.name, ap.name".
  • SQL , , , , GROUP BY.
  • , "" GROUP BY...
+1

OK, maybe something like this:

SELECT name, iata, icao, apid, x, y
  FROM airports
  WHERE y = (SELECT MAX(A.y)
               FROM airports AS a
             INNER JOIN flights AS f
               ON (F.SRC_APID = A.APID OR
                   F.DST_APID = A.APID)
               WHERE f.uid = [user_id]) AND
        apid IN (SELECT SRC_APID AS APID
                   FROM FLIGHTS
                   WHERE UID = [user_id]
                 UNION ALL
                 SELECT DEST_APID AS APID
                   FROM FLIGHTS
                   WHERE UID = [user_id])

It is impossible to guarantee how this will be carried out, but perhaps this is a step in the right direction.

Share and enjoy.

+1
source

Source: https://habr.com/ru/post/1739836/

More articles:


All Articles