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
How do I do an inner join in these tables:
Vehicle
Brand
I want to pull in a table vei_name, vei_cor and mar_name
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
.
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