SQL three tables using only JOIN-CodePudding

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.

CodePudding user response：

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

CodePudding user response：

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