CODEWITHSUNDEEP
sqlinner-join
Valery Fludkov
Valery Fludkov

Reputation: 135

SQL Query Self Join from two tables

I`m stuck on the following problem. There are two tables:

banks

bank_id [PK] | name
---------------------
 1            Bank_1
 2            Bank_2
 3            Bank_3
 4            Department_1
 5            Department_2
 6            Department_3

relations 

id [PK] | parent_id | filial_id
--------------------------------   
 1         1            5
 2         2            4
 3         2            6

Need to select all pairs of Banks-Departments. Banks without departments should be also selected.

I wrote following query:

SELECT A.name AS 'Bank', B.name  AS 'Department'
FROM banks A,
     banks B 
        JOIN relations ON B.bank_id=relations.filial_id
                      AND A.bank_id = relations.parent_id;

But it displays only banks with department. How should I change my query to output string Bank_3 which has not a department??

Upvotes: 2

Views: 2032

Answers (4)

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

Departments are all "banks" that exists in relations.filial_id. Banks are all "banks" that don't. Use outer joins to get the departments for a bank, so as to also keep banks without departments.

select b.name as bank_name, d.name as department_name 
from (select * from banks where bank_id not in (select filial_id from relations) b
left join relations r on r.parent_id = b.bank_id
left join banks d on d.bank_id = r.filial_id

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133360

You could do with an union 

SELECT A.name AS 'Bank', B.name  AS 'Department' 
FROM  relations AS R 
INNER JOIN banks AS A ON A.bank_id = R.parent_id
INNER  JOIN banks AS B ON  B.bank_id=R.filial_id ;

UNION 

select name, null
from banks where bank_id not in (select parent_id from relation)

Upvotes: 1

Tajinder
Tajinder

Reputation: 2338

I think your table should be as given below

  1. Bank table will only include bank_id , bank name. In which bank_id will be primary key.
  2. There will be separate table for the departments which include bank_id , dept_id , dept_name
  3. bank_id in departments table will be foreign key from banks table
  4. dept id will be primary key in departments table

Your banks table will be divided as given below.

    bank_id  bank_name
    1            Bank_1
    2            Bank_2
    3            Bank_3

    bank_id dept_id dept_name
       1     4            Department_1
       2     5            Department_2
       2     6            Department_3

Then you can simply use left join to get data in required format.

Currently, in your banks table there is no identification column for bank and department.

Upvotes: 2

Stephen
Stephen

Reputation: 1542

Try this:

SELECT A.name AS 'Bank', B.name  AS 'Department' 
FROM (SELECT * FROM banks WHERE NOT EXISTS(SELECT TOP 1 1 FROM Relations WHERE filial_id = bank_id)) A
LEFT OUTER JOIN Relations r 
INNER JOIN banks B ON B.bank_id= r.filial_id ON   A.bank_id = r.parent_id

Output:

Bank                 Department
-------------------- --------------------
Bank_1               Department_2
Bank_2               Department_1
Bank_2               Department_3
Bank_3               NULL

Upvotes: 1

Related Questions