Robot Robot - 6 months ago 17
MySQL Question

I want to Query in MYSql with some conditions

Here is question with conditions:

In a somewhat related situation to the above, management wants to know which technicians are certified in one trade but NOT in another trade. Pick any two trades of your choice and display the technician last name, first name and job title.

Here are my tables:

TECHNICIAN TABLE:


technician_nbr, last_name, first_name, title, address, phone, salary, shift, manager_nbr '4701', 'Doug', 'Maxim', 'Jr Tech', '6
street', '6042233645', '78900.00', 'NOR', '901'

'4702',
'McKennan', 'Brydon', 'Sr Tech', '7 street', '6043349556', '89000.00',
'EAR', '901'

'4703', 'Zhou', 'Sissy', 'Tr Tech', '8 street',
'6044455967', '70100.00', 'NOR', '901'

'4704', 'Good', 'Magdeline',
'Sr Tech', '9 street', '6045567778', '82300.00', 'EAR', '901'

'4705', 'Bitty', 'Nicholas', 'T Tech', '5 street', '6046677289',
'42300.00', 'LAT', '901'


SPECIALIZES TABLE:


technician_nbr, trade_type, date_certified

'4701', 'AirFrame', '2010-03-16'

'4701', 'Avionics', '2011-06-16'

'4701', 'Engine',
'2010-07-16'

'4702', 'Airframe', '2010-01-18'

'4702', 'Hydraulics',
'2011-09-16'

'4702', 'Interior', '2010-02-02'

'4703', 'Avionics',
'2010-08-08'

'4703', 'Engine', '2011-10-12'

'4703', 'Hydraulics',
'2011-09-19'


HERE IS MY QUERY:

SELECT DISTINCT last_name, first_name, title, shift
FROM technician, specializes
WHERE trade_type='Hydraulics' OR trade_type = 'AirFrame'
GROUP BY first_name
HAVING shift='NOR'


HERE IS THE RESULT OF MY QUERY:


last_name, first_name, title, shift

'Doug', 'Maxim', 'Jr Tech', 'NOR'

'Zhou', 'Sissy', 'Tr Tech', 'NOR'


Now, I want to tell you my confusion. Although, I am optimistic that the result of my query is right because right after reading the above question description I looked at the tables selected two types of trades my self and found these two persons are only certified in one trade.

I somehow wanted to show them using the query so I tried to grouped them by "NOR" Shift in HAVING CLAUSE. Results were successful but the problem is that what I did does not called a query because I intentionally set the Shift to "NOR" in HAVING CLAUSE that way it will definitely show these only because they only have "NOR" shift.

I want a query that can show these two persons like I or You never saw these persons in table and query will produce these results. Thanks a lot for your effort.

Answer

If i understand right your question you should use this query

SELECT last_name, first_name, title, shift  
FROM technician
WHERE technician_nbr in (select technician_nbr 
         from specializes  
         where trade_type in ('Hydraulics' , 'AirFrame'  )) /*  list of trade_type need */
AND  technician_nbr not in   (select technician_nbr 
         from specializes  
         where trade_type in ('Engine'  ));   /*  list of not trade_type need */