当前位置: 动力学知识库 > 问答 > 编程问答 >

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??

网友答案:

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)
网友答案:

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.

网友答案:

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
网友答案:

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
分享给朋友:
您可能感兴趣的文章:
随机阅读: