Que tal assim:
SELECT Passagens.*, Cidades.nome_cidade, Estados.sigla, Passageiros.nome_passageiro, Passageiros.rg,
Passagens.data_ida, Passagens.data_volta FROM Passagens_Acompanhante
INNER JOIN Passagens ON Passagens_Acompanhante.passagem_id = Passagens.id
INNER JOIN Cidades ON Passagens.cidade_id = Cidades.codigo_cidade
INNER JOIN Estados ON Cidades.codigo_uf = Estados.codigo_estado
ORDER BY Passagens;
Supondo as tabelas:
/*
* Estados
*/
CREATE TABLE IF NOT EXISTS Estados(
codigo_estado INT NOT NULL AUTO_INCREMENT,
nome_estado VARCHAR(20) NOT NULL,
sigla VARCHAR(2) NOT NULL UNIQUE,
CONSTRAINT pk_estado PRIMARY KEY(codigo_estado)
)CHARACTER SET utf8 COLLATE utf8_general_ci;
/*
* Cidades
*/
CREATE TABLE IF NOT EXISTS Cidades(
codigo_cidade INT NOT NULL AUTO_INCREMENT,
nome_cidade VARCHAR(35) NOT NULL,
codigo_uf INT NOT NULL,
CONSTRAINT pk_cidade PRIMARY KEY(codigo_cidade)
CONSTRAINT fk_estado_cidade FOREIGN KEY(codigo_uf) Estados(codigo_estado)
)CHARACTER SET utf8 COLLATE utf8_general_ci;
/*
* Passageiros
*/
CREATE TABLE IF NOT EXISTS Passageiros(
id_passageiro INT NOT NULL AUTO_INCREMENT,
nome_passageiro VARCHAR(30) NOT NULL,
rg VARCHAR(11) NOT NULL,
CONSTRAINT pk_passageiro PRIMARY KEY(id_passageiro)
)CHARACTER SET utf8 COLLATE utf8_general_ci;
/*
* Passagens
*/
CREATE TABLE IF NOT EXISTS Passagens(
id INT NOT NULL AUTO_INCREMENT,
data_ida DATE NOT NULL,
data_volta DATE NOT NULL,
descricao VARCHAR(255),
cidade_id INT NOT NULL,
passageiro_id INT NOT NULL,
CONSTRAINT pk_passagem PRIMARY KE(id)
)CHARACTER SET utf8 COLLATE utf8_general_ci;
/*
* Passagens_acompanhante
*/
CREATE TABLE IF NOT EXISTS Passagens_Acompanhante(
codigo_pass_acomp INT NOT NULL AUTO_INCREMENT,
passagem_id INT NOT NULL,
acompanhante_id INT NOT NULL,
CONSTRAINT pk_pas_acomp PRIMARY KEY(codigo_pass_acomp),
CONSTRAINT fk_pas_acomp FOREIGN KEY(id_passagem) REFERENCES Passagem(id)
)CHARACTER SET utf8 COLLATE utf8_general_ci;
Mas eu identifiquei alguns problemas na modelagem… Por exemplo, uma passagem tem uma cidade de origem e uma cidade de destino, mas só registra uma cidade na tabela Passagens.
Obs.: eu costumo padronizar da seguinte forma:
1 - nomes de tabelas são grafadas no plural;
2 - campos de identificação de pessoas uso ID, dos restante uso código.
