Good evening guys! I have a database with the tables:
- PASSENGERS
- DRIVERS
- TRAVEL
- A driver is assigned for each trip.
- More than one passenger can go on the same trip.
Doubt: How do I refer to more than one passenger on the same trip by FK?
Good evening guys! I have a database with the tables:
Doubt: How do I refer to more than one passenger on the same trip by FK?
You can create a many-to-many table, and reference the trip and passengers, I will post an example.
Note: I did not create the table with pk's
nor fk's
, because it is only for you to understand the modeling and query's
.
CREATE TABLE passageiros(
id INT,
nome VARCHAR(50)
);
INSERT INTO passageiros VALUES
(1,'Passageiro 1'),(2,'Passageiro 2'),(3,'Passageiro 3'),(4,'Passageiro 4'),(5,'Passageiro 5');
CREATE TABLE motoristas(id INT, nome VARCHAR(50));
INSERT INTO motoristas VALUES
(1,'Motorista 1'),(2,'Motorista 2'),(3,'Motorista 3');
CREATE TABLE viagens(id INT, id_motorista INT, descricao VARCHAR(50));
INSERT INTO viagens VALUES
(1,2,'Viagem 1'), (2,3,'Viagem 2');
CREATE TABLE passageiros_viagens(id INT, id_passageiro INT, id_viagem INT);
INSERT INTO passageiros_viagens VALUES
(1,1,1),(2,3,1),(3,5,1),(4,2,2),(5,5,2);
If I want to know the passengers that are on trip 1, I execute the following query:
SELECT
p.*
FROM viagens v
INNER JOIN motoristas m
ON m.id = v.id_motorista
INNER JOIN passageiros_viagens pv
ON pv.id_viagem = v.id
INNER JOIN passageiros p
ON p.id = pv.id_passageiro
WHERE v.id = 1;
Or I can use it this way using GROUP_CONCAT
and bring it in just one line for better visualization:
SELECT
v.descricao,
m.nome,
GROUP_CONCAT(p.nome SEPARATOR ', ') AS passageiros
FROM viagens v
INNER JOIN motoristas m
ON m.id = v.id_motorista
INNER JOIN passageiros_viagens pv
ON pv.id_viagem = v.id
INNER JOIN passageiros p
ON p.id = pv.id_passageiro
WHERE v.id = 1;