SQL JOIN multiple tables

Question

SQL JOIN multiple tables

Navigation

#1 by (2 votes)

2

I have 4 tables that I need to extract the results, but I always get duplicate records, can anyone help?

tabclientes:
codcli nome
1       A
2       B
3       C


tabrepre:
codrepre nome
1         AA
2         AB
3         AC

tabvendedor:
codrepre  codvend  nome
1          1        BA
1          2        BB
2          1        BC
3          1        BD

tabrepcli
codcli  codrepre codvend
1        1         2
2        1         1
3        3         1

My SQL:

SELECT
     tabclientes.nome,
     tabvendedor.nome
FROM
     tabclientes 
     inner join tabrepcli USING (codcli)
     inner join tabrepre USING (codrepre)
     inner join tabvendedor USING (codvend)

The result duplicates the records of the client name. I've tried distinct but it did not work.

sql join postgresql inner-join

asked by anonymous 10.07.2018 / 23:00

1 answer

How to merge 2 or more Json Objects into 1 Only Using links with PDO in PHP

score 2 · Accepted Answer

First I'll create the tables, just as you mentioned them, populating them:

Client table

postgres=# create table tabclientes (codcli int, nome varchar(10));
CREATE TABLE
postgres=# insert into tabclientes (codcli, nome) values (1, 'A'), (2, 'B'), (3, 'C');
INSERT 0 3

Table repre

postgres=# create table tabrepre (codrepre int, nome varchar(10));
CREATE TABLE
postgres=# insert into tabrepre (codrepre, nome) values (1, 'AA'), (2, 'AB'), (3, 'AC');
INSERT 0 3

Salesperson table

postgres=# create table tabvendedor (codrepre int, codvend int, nome varchar(10));
CREATE TABLE
postgres=# insert into tabvendedor (codrepre, codvend, nome) values (1, 1, 'BA'), (1, 2, 'BB'), (2, 1, 'BC'), (3, 1, 'BD');
INSERT 0 4

Table uniting everyone

postgres=# create table tabrepcli (codcli int, codrepre int, codvend int);
CREATE TABLE
postgres=# insert into tabrepcli (codcli, codrepre, codvend) values (1, 1, 2), (2, 1, 1), (3, 3, 1);
INSERT 0 3

Once this is done, let's look at your table. The tabreprcli table joins the customer, the seller, and the representative (I think that's the meaning). When we put the representative and the client through this table, the results appear ok.

SELECT
    tabclientes.nome, 
    tabrepre.nome 
FROM
    tabclientes 
    inner join tabrepcli using (codcli) 
    inner join tabrepre using (codrepre);

The result is as follows:

 nome | nome
------+------
 A    | AA
 B    | AA
 C    | AC
(3 rows)

No repetition. But when we run your query, we get the result repeated. Looking at the values of the seller table, actually, the codvend is repeated, will soon repeat the data. But also looking at the code, we see that the union of the 2 fields (codvend, codrepre) are unique, making them a key. If we change our query to take this into consideration, the result is correct.

SELECT
    tabclientes.nome, 
    tabvendedor.nome
FROM 
    tabclientes 
    INNER JOIN tabrepcli USING (codcli) 
    INNER JOIN tabrepre USING (codrepre) 
    INNER JOIN tabvendedor USING (codvend, codrepre);

The result is correct:

 nome | nome 
------+------
 B    | BA   
 A    | BB   
 C    | BD