SQL JOIN multiple tables

Asked

Viewed 217 times

2

I have 4 tables that I need to extract the results, but I always get duplicate records, someone can 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 client name records. I have tried distinct but did not resolve.

sql postgresql join inner-join

  • what outcome you expect?

  • I expect the list of customer names with the seller or first seller.(There are other fields and tables involved in select, but these are giving headache). It is currently duplicating customer data lines for each representative.

1 answer

2


First I will create the tables, in the same way that Voce mentioned ae, 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

Seller 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 else

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 table tabreprcli joins the customer, the seller and the representative (I think that’s what it means). When we join 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 execute your query, we have the result repeated. Looking at the values of the seller table, really, the codvend is repeated, soon will repeat the data. But also looking at the codrepre, 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 account, 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

Browser other questions tagged sql postgresql join inner-join

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