Querying multiple tables in mysql

Question

Querying multiple tables in mysql

Navigation

#1 by (3 votes)

1

My template is as follows, 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 link with the flights table and model, and the model table is with a 1: M link with manufacturer. I intend to make a query of the genre "Which Boeing aircraft were built before the year 2000 that had flights canceled". Boeing is the name of the manufacturer, and for flights canceled in 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 aircraft lower than 2000, as I do now go to the flights table and bring the canceled = 1. It still causes me some confusion these trips to several tables, can anyone help me?

mysql query

asked by anonymous 16.09.2015 / 00:12

1 answer

Problems converting data mon dd yyyy hh: mm Add values of an inner join to 2 variables

score 3 · 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 useful 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