How to make INNER JOIN mysql

Asked

Viewed 19,092 times

2

How do I make an Inner Join in these tables:

Vehicle

  • ve_id
  • ve_name
  • vey_cor
  • mar_id (foreign key)

Brand

  • mar_id
  • mar_nome

I want to pull in a table ve_name, ve_color and mar_name

mysql

2 answers

3

Thus:

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 the query above Veiculo was dubbed to a and Marca was dubbed to b, then a.mar_id is the same thing as Veiculo.mar_id.

See more about INNER JOIN.

  • I put the code here, but it just pulled the ve_name and ve_color. Did not pull the mar_name

  • mar_name is filled in?

  • yes. has a record

  • Put in your question what is being returned, and also put the code you are using.

  • Show, I just wanted to remind you how does the Inner Join and his example with the nicknames was the most top (of course I didn’t get to look at the documentation, but clearer than his example to remember impossible... rs). Vlw!

2

You can do the Inner Join bringing all the table records as well as can bring only 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 is how the result should be

Resultado da pesquisa

Or you can select only the records of a table and one more field

Using the table name .*

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

These codes have been tested on http://sqlfiddle.com

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

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.