SQL three tables using only JOIN

Question

I have three tables: T1 has id's from T2 (client) and T3 (supplier), it also acts a black list: T1's rows with client's and supplier's id.

I want to get the supplier's id the client can buy from.

.

currently T1 has three rows:

T1_id | T2_id | T3_id
   1      1       3 
   2      1       4 
   3      2       3

I tried:

select T3.id 
from T1 
left join T2 on T2.id = T1.T2_id
right join T3 on T3.id != T1.T3_id 
where T2.id = 1

the output is:

It should be only 1 and 2. What am I missing? It works fine with T2.id = 2.

I was able to solve it performatically like this: select T3.id from T3 where not exists ( select T1.T3_id from T1 inner join T2 on T2.id = T1.T2_id and T1.T3_id = T3.id where T2.id = 1 ) — Vinícius OA, Jul 05 '22 at 17:20

score 0 · Answer 1 · answered Jul 05 '22 at 14:51

0

You can also do this like that

select T3.id from T1, T2, T3
where 
T1.T2_id = T2.id and
T3.id <> T1.T3_id and
T2.id = 1

I hope this helps

answered Jul 05 '22 at 14:51

PDA3

16
1

1

I think you should consider learning `join` syntax. See [bad-habits-to-kick-using-old-style-joins](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) – Shmiel Jul 05 '22 at 15:03

score 0 · Answer 2 · answered Jul 05 '22 at 14:52

So you have

List of clients
List of suppliers
A map between clients and suppliers.

This should be your table layout.

clients
---------
client_id [PK]
client_label

suppliers
---------
supplier_id [PK]
supplier_label

client_suppliers
----------------
client_supplier_id [PK]
client_id 
supplier_id

Then you just select which suppliers are mapped to a particular client.

SELECT 
    supplier_id
    , supplier_label
FROM 
    suppliers T1 
INNER JOIN 
    client_suppliers T2 ON T2.supplier_id = T1.supplier_id
WHERE 
    T2.client_id = 1

SQL three tables using only JOIN

2 Answers2