Query to multiple tables in mysql

Question

My model is the following, I have 4 tables

Avião (tailnum (PK), year)
Voos (id_voo (PK), cancelled)
Modelo (idmodel (PK), nome)
Fabricante (id_fabricante (PK), nome)

Airplane is with a 1:M connection with the flight chart and model, and the model table is with a 1:M connection with manufacturer. I intend to make a query of the genre "Which Boeing aircraft built before the year 2000 that had canceled flights". Boeing is the name of the manufacturer, and in relation to flights canceled on the flight table is a boolean and that when the value = 1 the flight was canceled. Then the year is on the plane table.

I’m doing it this way:

Select tailnum
From
      (Select t.tailnum
       From Aviao t
       Inner Join Modelo M
       ON t.tailnum = M.tailnum
       Inner Join Fabricante C
       ON M.idmodel = C.idmodel
       Where nome = "Boeing"
       group by t.tailnum )
Where year < 2000 and tailnum IN (Select tailnum
                                  From voos
                                  where cancelled = 1)

In this I will get the Boeing planes below 2000, as I do to now go to the table flights and bring the cancelled = 1. Still causes me some confusion these going to various tables, will someone help me?

Answer 1

I was able to perform the query as follows:

select distinct
   a.tailnum
from
   aviao a,
   voos v,
   fabricante f,
   modelo m
 where
   a.year < 2000 and
   f.nome = 'Boeing' and
   v.cancelled = 1 and
   a.tailnum = m.tailnum and
   f.id_fabricante = m.id_fabricante and
   a.tailnum = v.tailnum

See if it’s any use to you.

Sqlfiddle

@EDIT

Using Joins, which is the most recommended form:

select 
   a.tailnum
from
   aviao a
   left join voos v 
   on a.tailnum = v.tailnum
   inner join modelo m 
   on m.tailnum = v.tailnum
   inner join fabricante f 
   on f.id_fabricante = m.id_fabricante   
 where
   a.year < 2000 and
   f.nome = 'Boeing' and
   v.cancelled = 1

SQL Fiddle