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

sql server - need to fetch specific data from Left outer join

问题描述:

Let suppose i have four table

Main, Parent , child, parentchildmapping with below Structure

StageId will be ParentId or ChildId if ConfigTable will column value Parent or Child.

Now i have to perform left outer join on Main table using all other table and wants data like

ResultSet

  • MT.ID
  • PT.Code
  • CH.Code

For eg:-

then Result should be like

  • 1 PT01 null
  • 2 PT01 CH01

till now my query was

select MT.ID, PT.code, CH.code from Maintable MT

left outer join Parent PT on MT.SatgeId = PT.ID and MT.ConfigTable = 'Parent'

left outer join child CH on MT.SatgeId = CH.ID and MT.ConfigTable = 'Child'

right i am getting output like

  • 1 PT01 null
  • 2 null CH01

网友答案:

You need to use mapping table in order to select parent by child. Here is how to do this:

DECLARE @mt TABLE(ID INT, StageID INT, ConfigTable VARCHAR(10))
DECLARE @pt TABLE(ID INT, Code VARCHAR(10))
DECLARE @ct TABLE(ID INT, Code VARCHAR(10))
DECLARE @map TABLE(ID INT, ParentID INT, ChildID INT)

INSERT INTO @mt VALUES(1, 1, 'Parent'),(2, 1, 'Child')
INSERT INTO @pt VALUES(1, 'PT01')
INSERT INTO @ct VALUES(1, 'CH01')
INSERT INTO @map VALUES(1, 1, 1)

SELECT mt.ID, 
       ISNULL(pt.Code, ptp.Code) AS PCode, 
       ct.Code AS CCode
FROM @mt mt
LEFT JOIN @pt pt ON mt.ConfigTable = 'Parent' AND mt.StageID = pt.ID
LEFT JOIN @ct ct ON mt.ConfigTable = 'Child' AND mt.StageID = ct.ID
LEFT JOIN @map map ON ct.ID = map.ChildID
LEFT JOIN @pt ptp ON map.ParentID = ptp.ID

Output:

ID  PCode   CCode
1   PT01    NULL
2   PT01    CH01
分享给朋友:
您可能感兴趣的文章:
随机阅读: