How to make INNER JOIN between two tables?

Asked

Viewed 108 times

0

I need to join the fields of the table "employees" and "clients". The problem is that the data repeats,: "

I believe it has something to do with a foreign key and a primary key. The first "name" field belongs to the "employees" table, the other belongs to "clients".

create database meuBanco;

 use meuBanco;
 create table Produtos(
 codigo int(10), 
 nome varchar(20), 
 valor float, 
 quantidade_em_estoque int(10), 
 ativo enum('S','N'),
primary key(nome)
 );


  create table Clientes(
 codigo int(10), 
 nome varchar(20),
 endereco varchar(50),
primary key(nome)
 );
 
 
  create table Funcionarios(
 codigo int(10), 
 nome varchar(20),
 cargo varchar(20), 
 ativo enum('S','N'),
 primary key(nome)
 );
 
 create table vendas(
  codigo_venda int(10), 
  codigo_cliente int(10),
  codigo_vendedor int(10), 
  data_completa_de_venda date
 
 );
 create table detalhe_da_venda( 
 codigo_venda int(10), 
 codigo_produto int(10),
 quantidade_de_produto_vendido int(10)
 
 );

insert into Produtos value
(1,'JANELA','134.56','10','S'),
(2,'FOGAO','334.56','20','N'),
(3,'COMPUTADOR','15.6','30','S'),
(4,'CELULAR','114.76','40','N'),
(5,'CADEIRA','184.96','50','S');


insert into Clientes value
(6,'ALBERTO','Av. Presidente Wilson - 412'),
(7,'BRUNO','Av. Bartolomeu De Gusmão - 211'),
(8,'CARLOS','Av. Ana Costa - 416'),
(9,'DANILO','Av. Padre Anchiete - 718'),
(10,'ESTER','Av. Marechal Deodoro - 702');

insert into Funcionarios value
(11,'ALESSANDRA','VENDEDOR','S'),
(12,'BRENO','GERENTE','N'),
(13,'CARLOS','ENTREGADOR','S'),
(14,'DANIEL','MARCENEIRO','N'),
(15,'ELENA','VENDEDORA','S');

insert into Vendas value
(16,001,111,'2019-05-01'),
(17,002,222,'2015-04-10'),
(18,003,333,'2018-07-09'),
(19,004,444,'2013-04-03'),
(20,005,555,'2019-10-12'),
(21,006,666,'2019-10-02'),
(22,007,777,'2019-10-31'),
(23,008,888,'2019-05-12'),
(24,009,999,'2019-10-29'),
(25,0010,101010,'2019-06-12');


insert into detalhe_da_venda value
(26,00070,5),
(27,00080,23),
(28,00090,2),
(29,000010,1),
(30,000011,3),
(31,000034,34),
(32,000043,22),
(33,00044,11),
(34,000022,45),
(35,000000,4);

select*from detalhe_da_venda;


select * from vendas;





alter table vendas
add column qt_produtos_vendidos int(10);




select 
funcionarios.nome,
clientes.nome
from funcionarios join clientes;

mysql

  • To make an INNER JOIN you need to specify the fields of each of the tables that will have equal values. In their tables I could not identify such fields. If there is no value common to both tables what you will have, as happened in your example, a Cartesian product.

  • The problem isn’t Primary Key or Foreign Key. It’s duplicating because for every query table record, it’s returning one of the table rows used in Join. Review your Join concept and the logic of relating customer and employee?

  • What I’m going to say now has nothing to do with the Join problem, but with setting the Primary Keys of your tables. Your tables have the field codigo, which is the field supposed to be the key Primary. To ensure that the name does not repeat, you can create unique index for the name field.

  • What is the question you need to answer? So it is easier to guide you.

2 answers

1

If I understand correctly, you want to join the tables that have the same name, correct?

You can try something like:

SELECT nome_coluna FROM Clientes INNER JOIN Funcionarios ON Clientes.nome = Funcionarios.nome;

It is not very recommended to use the name for this. The best would be a unique value (maybe code).

-2

As the comrade said above, it is not mt used the Join p/ make junction of tables where you want to seek the junction of characters that are equal. I put a code below p vc make the junction of the client table with the sale, where you search the code.

SELECT 
a.codigo,
a.nome,
b.nome
FROM cliente a 
INNER JOIN venda b
ON a.codigo=b.codigo
ORDER BY 1;

Browser other questions tagged mysql

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