CODEWITHSUNDEEP
mysqlsqldatabase
Fred
Fred

Reputation: 45

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.

Upvotes: 2

Views: 89

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269703

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)

Upvotes: 2

amaksr
amaksr

Reputation: 7745

SELECT reg_nbr
FROM lots
GROUP BY reg_nbr
HAVING COUNT(DISTINCT lot_nbr) = 1

Upvotes: 2

Related Questions