Fred Fred - 6 months ago 22
MySQL Question

Interesting Query in MySQL with conditions

Here is the question:

Management wants to know which specific aircraft have always been assigned to the same hangar lot (each aircraft is assigned to a lot for only a year and then renewed if requested. But the lot may or may not be the same lot upon renewal; the pk for lots table is registration number and date assigned. List the aircraft registration number, hangar location, hangar capacity. Each aircraft should appear only once in your report.

Here is lots table:


lot_nbr, hangar_nbr, reg_nbr, date_assigned

'7706', '1', '505', '2011-07-17'

'7707', '1', '505', '2012-07-18'

'7708', '1', '505', '2013-12-22'

'8801', '2', '101', '2011-01-22'

'8802', '2', '101', '2012-01-22'

'8803', '2', '303', '2011-01-10'

'8803', '2', '202', '2011-04-12'

'8803', '2', '202', '2012-04-12'

'9902', '3', '303', '2012-01-10'

'9904', '3', '404', '2011-01-10'

'9905', '3', '404', '2012-01-10'


Here is hangar table:


hangar_nbr, location, capacity, manager_nbr

'1', 'South RW', '24', '902'

'2', 'North RW', '12', '902'

'3', 'West RW', '25', '902'


Here is aircraft table: (Might be needed)


reg_nbr, plane_type, manufacturer, resident, date_service

'101', 'SE', 'Prop', 'Lear', '1', '1998-12-01'

'202', 'DE Prop', 'Cessna', '1','1999-11-22'

'303', 'SE Jet', 'Bombardier', '1', '2000-07-06'

'404','ME Jet', 'Lear', '1', '2001-02-24'

'505', 'SE Prop', 'Cessna', '0','2003-12-01'

'606', 'DE Prop', 'Bombardier', '1', '2004-11-22'

'707','SE Jet', 'Lear', '1', '2005-07-06'

'808', 'ME Jet', 'Cessna', '0','2005-02-24'

'909', 'SE Prop', 'Bombardier', '1', '2002-02-12'


The query I want (Exactly):


reg_nbr, location, capacity

'202', 'North RW', '12'


Here is what I tried:

SELECT lots.reg_nbr, location, capacity
FROM hangar, lots
WHERE lots.hangar_nbr = hangar.hangar_nbr AND reg_nbr='202'
GROUP BY lots.reg_nbr


My Problem:

As shown above I wanted to show aircraft with
reg_nbr '202'
in my result because it is the only aircraft that has been allocated the same lot_nbr every time but unfortunately the way I did i.e writing
reg_nbr ='202'
in WHERE clause is not the way query should be done. I want to show how get this results but with a real and legal query. Thanks a lot for your effort.

Answer

Presumably, reg_nbr is for the aircraft. You can get the aircraft with a using a single table, group by, and having clause:

SELECT reg_nbr, hangar_nbr, lot_nbr
FROM lots 
GROUP BY reg_nbr
HAVING MAX(hangar_nbr) = MIN(hangar_nbr) AND
       MAX(lot_nbr) = MIN(lot_nbr)
WHERE lots.hangar_nbr = hangar.hangar_nbr AND reg_nbr='202'
GROUP BY lots.reg_nbr 
HAVING COUNT(*) = 1;
Comments