SQL Query Search 2 tables

Question

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

MySQL or SQL-Server, which is it? – Barmar Aug 26 '16 at 20:08 — Barmar, Aug 26 '16 at 20:08
`= (SELECT ...)` should be `IN (SELECT ...)`. – Barmar Aug 26 '16 at 20:09 — Barmar, Aug 26 '16 at 20:09

Hurricane Development · Accepted Answer · 2016-08-26T23:59:12.250

0

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;

edited Aug 26 '16 at 23:59

answered Aug 26 '16 at 20:10

Hurricane Development

2,449
1
19
40

I have just tried SELECT * FROM phpvms_aircraft INNER JOIN phpvms_airframe_qual ON phpvms_airframe_qual.icao = phpvms_aircraft.icao WHERE phpvms_airframe_qual.pilotid = 1001; and it comes up with #1267 - Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (latin1_general_ci,IMPLICIT) for operation '=' – Scott Daniels Aug 26 '16 at 20:15
@ScottDaniels looks like your tables have different collations. http://stackoverflow.com/a/5747047/2696950 will tell you how to fix it – Hurricane Development Aug 26 '16 at 20:18
1

Thanks That was the problem got it to work now THANKS ALOT!! :) – Scott Daniels Aug 26 '16 at 20:24
Why accept an answer with a syntax error? I don't get it. – Strawberry Aug 26 '16 at 23:09
@Strawberry he must have noticed the error I made and fixed it when he tested the query. Maybe he just saw my answer and wrote his own query based off my answer, but he left out my error. In the future, maybe you can make an edit to my answer to fix the mistake. I fixed it now though, thanks for pointing it out. – Hurricane Development Aug 27 '16 at 00:00

score 0 · Answer 2 · answered Aug 26 '16 at 20:13

0

You can do an inner join to get this answer:

 select * from phpvms_aircraft inner p join phpvms_airframe_qual pq on p.icao = pq.icao where ...

answered Aug 26 '16 at 20:13

Kannan Kandasamy

13,405
3
25
38

SQL Query Search 2 tables

2 Answers2