Scott Daniels Scott Daniels - 3 months ago 6
MySQL Question

SQL Query Search 2 tables

Hi Im stuck trying to search two tables and getting the results I need I have one table with qualifications in with the users id's as well set out like this:

Table name: phpvms_airframe_qual

id pilotid airframe icao
0 1001 name tutr
1 1001 name eufi
2 1002 name tutr
3 1002 name king
4 1001 name a400


Table name: phpvms_aircraft

id fullname icao registration
0 tutor tutr G-BXEX
1 typhoon eufi ZJ932
2 tutor tutr G-DXRT
3 kingair king ZZ990
4 atlas a400 ZM400


What I need to do is get a list of aircraft but only show ones that the user has the qualification for. So user 1001 will show the following from the example:

id fullname icao registration
0 tutor tutr G-BXEX
1 typhoon eufi ZJ932
2 tutor tutr G-DXRT
4 atlas a400 ZM400


and 1002 will show the following:

id fullname icao registration
0 tutor tutr G-BXEX
2 tutor tutr G-DXRT
3 kingair king ZZ990


I have tried:

SELECT * FROM `phpvms_aircraft` WHERE phpvms_aircraft.icao = (SELECT icao FROM `phpvms_airframe_qual` WHERE pilotid = '1001')


But I get errors any Ideas how to do this

Thanks

Answer

This should work but I have not been able to test it. let me know if it doesn't and what errors come up.

SELECT * FROM phpvms_aircraft,
    INNER JOIN phpvms_airframe_qual ON phpvms_airframe_qual.icao = phpvms_aircraft.icao
    WHERE phpvms_airframe_qual.pilot_id = 1001;
Comments