ABOUT MYSQL AND FOREIGN KEY

0

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?

    
asked by anonymous 12.04.2018 / 06:11

1 answer

0

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;
    
17.04.2018 / 17:06