How to do INNER JOIN mysql

1

How do I do an inner join in these tables:

Vehicle

  • vei_id
  • vei_name
  • vei_cor
  • mar_id (foreign key)

Brand

  • mar_id
  • mar_name

I want to pull in a table vei_name, vei_cor and mar_name

    
asked by anonymous 27.01.2018 / 14:26

2 answers

1

So:

SELECT Veiculo.vei_nome, Veiculo.vei_cor, Marca.mar_nome FROM Veiculo
INNER JOIN Marca ON Veiculo.mar_id = Marca.mar_id

You could also create nicknames for your tables, avoiding excessive typing:

SELECT a.vei_nome, a.vei_cor, b.mar_nome FROM Veiculo a
INNER JOIN Marca b ON a.mar_id = b.mar_id

In% with% over query was nicknamed for Veiculo and a was nicknamed for Marca , then b is same as a.mar_id .

See more about Veiculo.mar_id .

    
27.01.2018 / 14:27
0

You can make the inner join by bringing all the records in the table as well as you can only bring what you need, let's see:

Bringing all records from both tables

SELECT * FROM Veiculo
INNER JOIN Marca ON Veiculo.mar_id = Marca.mar_id

That way the result should be like this

Oryoucanselectonlyrecordsfromatableandafield

Usingthetablename.*

SELECTVeiculo.*,Marca.mar_nomeFROMVeiculoINNERJOINMarcaONVeiculo.mar_id=Marca.mar_id

Thesecodeshavebeentestedonthe link

Schema

CREATE TABLE marca (
    mar_id int NOT NULL PRIMARY KEY,
    mar_nome varchar(40)
);

create table veiculo (vei_id int NOT NULL, 
  vei_nome varchar(40),
  mar_id int(30),
  PRIMARY KEY (vei_id),
  FOREIGN KEY (mar_id) REFERENCES marca(mar_id));

insert into marca values(1, 'Volkvagen');
insert into marca values(2, 'Ford');
insert into marca values(3, 'Chevrolet');


insert into veiculo values (1, 'Verona',2);
insert into veiculo values (2, 'Escort',2);
insert into veiculo values (3, 'Fusca',1);
insert into veiculo values (4, 'Caravan',3);
insert into veiculo values (5, 'Brasília',1);

SQL

SELECT Veiculo.*, marca.mar_nome FROM Veiculo
INNER JOIN Marca ON Veiculo.mar_id = Marca.mar_id
    
27.01.2018 / 16:07