write a query to get Desired SQL table output

Question

I have some tables and find the desired output with the following table using sql query. tables are:

EmpID   TabId
101   1
102   2
103   3
104   4
105   1

where table name is Master

EmpID   Name
101   A1
102   A2
103   A3
104   A4
105   A5

table name is TableA

EmpID   Name
101   B1
102   B2
103   B3
104   B4
105   B5

table name is TableB

EmpID   Name
101   C1
102   C2
103   C3
104   C4
105   C5

table name is TableC

EmpID   Name
101   D1
102   D2
103   D3
104   D4
105   D5

table name is TableD

TabId   Name
1     TableA
2     TableB
3     TableC
4     TableD

table name is AttMaster is provided to us and desired output is below

TabId   Name
101     A1
102     B2
103     C3
104     D4
105     A5

kindly help me how can I get desired output with sql query. I did not get any solutions to find the output.

Answer 1

This should work. Hope it helps

DECLARE @Master TABLE (EmpID INT, TabId INT)
DECLARE @TableA TABLE (EmpID INT, Name VARCHAR(10))
DECLARE @TableB TABLE (EmpID INT, Name VARCHAR(10))
DECLARE @TableC TABLE (EmpID INT, Name VARCHAR(10))
DECLARE @TableD TABLE (EmpID INT, Name VARCHAR(10))
DECLARE @AttMaster TABLE (TabId INT, Name VARCHAR(10))

INSERT INTO @Master VALUES
('101', '1'),
('102', '2'),
('103', '3'),
('104', '4'),
('105', '1')

INSERT INTO @TableA VALUES
('101', 'A1'),
('102', 'A2'),
('103', 'A3'),
('104', 'A4'),
('105', 'A5')

INSERT INTO @TableB VALUES
('101', 'B1'),
('102', 'B2'),
('103', 'B3'),
('104', 'B4'),
('105', 'B5')

INSERT INTO @TableC VALUES
('101', 'C1'),
('102', 'C2'),
('103', 'C3'),
('104', 'C4'),
('105', 'C5')

INSERT INTO @TableD VALUES
('101', 'D1'),
('102', 'D2'),
('103', 'D3'),
('104', 'D4'),
('105', 'D5')

INSERT INTO @AttMaster VALUES
('1', 'TableA'),
('2', 'TableB'),
('3', 'TableC'),
('4', 'TableD')


;WITH cte_MasterAttMaster AS
(
SELECT m.EmpID ,am.TabId, am.Name
FROM @Master m
JOIN @AttMaster am
on m.TabId = am.TabId
)
,cte_TableABCD(EmpID,Name,TName) AS
(
SELECT *,'TableA' FROM @TableA 
UNION ALL
SELECT *,'TableB' FROM @TableB 
UNION ALL
SELECT *,'TableC' FROM @TableC 
UNION ALL
SELECT *,'TableD' FROM @TableD
)
SELECT mam.EmpID,
      tb.Name
FROM cte_MasterAttMaster AS mam
    JOIN cte_TableABCD AS tb
    ON tb.EmpID = mam.EmpID
    AND tb.TName = mam.Name
ORDER BY mam.EmpID

Answer 2

I'm sure, it's not a bad question to ask - and I think it's bad database design. You could get the desired results with the use of UNION. Updated the statement because of your changed question. It will be one join more per SELECT:

SELECT      a.EmpId AS TabId, a.Name
FROM        TableA a
INNER JOIN  master m ON a.EmpId = m.EmpID
INNER JOIN  attMaster am ON m.TABId = am.EmpId
WHERE       am.Name = 'TableA'

UNION

SELECT      b.EmpId, b.Name
FROM        TableB b
INNER JOIN  master m ON b.EmpId = m.EmpID
INNER JOIN  attMaster am ON m.TABId = am.EmpId
WHERE       am.Name = 'TableB'

UNION

SELECT      c.EmpId, c.Name
FROM        TableC c
INNER JOIN  master m ON c.EmpId = m.EmpID
INNER JOIN  attMaster am ON m.TABId = am.EmpId
WHERE       am.Name = 'TableC'

UNION

SELECT      d.EmpId, d.Name
FROM        TableD d
INNER JOIN  master m ON d.EmpId = m.EmpID
INNER JOIN  attMaster am ON m.TABId = am.EmpId
WHERE       am.Name = 'TableD'

ORDER BY    TabId

DEMO (for SQL Server 2008, but works with MySQL too). Try it.