Join multiple tables in SQL Server

hafifiw

SOLVED CLOSED ACTIVE ANSWERED
User: hafifiw
Posted: on Nov 22, 2020 10:32 PM
Forum: SQL Server
Answer: 2
Views: 1602

How to inner join 3 table in sql

I have 3 tables.

table 1: TankLine

Tank    Line 	Type
1	1A	CNT_A
1	1B	CNT_A
1	1C	CNT_A
2	2Q	CNT_A
2	2W	CNT_A
2	2E	CNT_A
3	3R	CNT_A
3	3T	CNT_A
3	3I	CNT_A
3	3P	CNT_A

Table 2: TankDetails

Tank 	Line 	Type	ID
1	1A	CNT_A	331
1	1B	CNT_A	311
2	2Q	CNT_A	122
3	3T	CNT_A	121
3	3I	CNT_A	144
3	3P	CNT_A	145

Table 3: TankRemark

ID	Remark
331	Y
311	Y
122	Y
121	Y
144	Y
145	Y

Download FREE API for Word, Excel and PDF in ASP.Net

nagaraju...

0 5

Reply
ANSWER
User: nagaraju60
Replied: on Nov 23, 2020 02:16 AM
Modified: on Nov 23, 2020 02:22 AM
Report

Hi @hafifiw,

Please refer to the below query.

-Under 'TankDetails' 'ID' Datatype is int then try

CREATE TABLE #TankLine(Tank INT, Line VARCHAR(20),Type varchar(20))  
INSERT INTO #TankLine VALUES(1, '1A','CNT_A')  
INSERT INTO #TankLine VALUES(1, '1B','CNT_A')  
INSERT INTO #TankLine VALUES(1, '1C','CNT_A')
INSERT INTO #TankLine VALUES(2, '2Q','CNT_A')
INSERT INTO #TankLine VALUES(2, '2W','CNT_A')
INSERT INTO #TankLine VALUES(2, '2E','CNT_A')
INSERT INTO #TankLine VALUES(3, '3R','CNT_A')
INSERT INTO #TankLine VALUES(3, '3T','CNT_A')
INSERT INTO #TankLine VALUES(3, '3I','CNT_A')
INSERT INTO #TankLine VALUES(3, '3P','CNT_A')


 
Create Table #TankDetails(Tank INT, Line VARCHAR(20),Type varchar(20),ID int)  
INSERT INTO #TankDetails VALUES(1, '1A','CNT_A',331)  
INSERT INTO #TankDetails VALUES(1, '1B','CNT_A',311)  
INSERT INTO #TankDetails VALUES(2, '2Q','CNT_A',122)
INSERT INTO #TankDetails VALUES(3, '3T','CNT_A',121)
INSERT INTO #TankDetails VALUES(3, '3I','CNT_A',144)
INSERT INTO #TankDetails VALUES(3, '3P','CNT_A',145)


Create Table #TankRemark (ID int,Remark char(1))  
INSERT INTO #TankRemark VALUES(331,'Y')  
INSERT INTO #TankRemark VALUES(311, 'Y')  
INSERT INTO #TankRemark VALUES(122, 'Y')
INSERT INTO #TankRemark VALUES(121, 'Y')
INSERT INTO #TankRemark VALUES(144, 'Y')
INSERT INTO #TankRemark VALUES(145, 'Y')




SELECT L.Tank,L.Line,L.Type,D.ID,
CASE WHEN D.ID IS NULL THEN '-' ELSE R.Remark End As Remark
FROM #TankLine L LEFT JOIN
#TankDetails D ON L.Line=d.Line LEFT JOIN
#TankRemark R ON D.ID=r.ID

-Under 'TankDetails' 'ID' Datatype is nvarchar then try

CREATE TABLE #TankLine(Tank INT, Line VARCHAR(20),Type varchar(20))  
INSERT INTO #TankLine VALUES(1, '1A','CNT_A')  
INSERT INTO #TankLine VALUES(1, '1B','CNT_A')  
INSERT INTO #TankLine VALUES(1, '1C','CNT_A')
INSERT INTO #TankLine VALUES(2, '2Q','CNT_A')
INSERT INTO #TankLine VALUES(2, '2W','CNT_A')
INSERT INTO #TankLine VALUES(2, '2E','CNT_A')
INSERT INTO #TankLine VALUES(3, '3R','CNT_A')
INSERT INTO #TankLine VALUES(3, '3T','CNT_A')
INSERT INTO #TankLine VALUES(3, '3I','CNT_A')
INSERT INTO #TankLine VALUES(3, '3P','CNT_A')


 
Create Table #TankDetails(Tank INT, Line VARCHAR(20),Type varchar(20),ID nvarchar(10))  
INSERT INTO #TankDetails VALUES(1, '1A','CNT_A',331)  
INSERT INTO #TankDetails VALUES(1, '1B','CNT_A',311)  
INSERT INTO #TankDetails VALUES(2, '2Q','CNT_A',122)
INSERT INTO #TankDetails VALUES(3, '3T','CNT_A',121)
INSERT INTO #TankDetails VALUES(3, '3I','CNT_A',144)
INSERT INTO #TankDetails VALUES(3, '3P','CNT_A',145)


Create Table #TankRemark (ID nvarchar(10),Remark char(1))  
INSERT INTO #TankRemark VALUES(331,'Y')  
INSERT INTO #TankRemark VALUES(311, 'Y')  
INSERT INTO #TankRemark VALUES(122, 'Y')
INSERT INTO #TankRemark VALUES(121, 'Y')
INSERT INTO #TankRemark VALUES(144, 'Y')
INSERT INTO #TankRemark VALUES(145, 'Y')


SELECT L.Tank,L.Line,L.Type,
CASE WHEN D.ID IS NULL THEN '-' ELSE D.ID End As ID,
CASE WHEN D.ID IS NULL THEN '-' ELSE R.Remark End As Remark
FROM #TankLine L LEFT JOIN
#TankDetails D ON L.Line=d.Line LEFT JOIN
#TankRemark R ON D.ID=r.ID

dharmend...

0 6

Reply
ANSWER
User: dharmendr
Replied: on Nov 23, 2020 04:20 AM
Report

Hi hafifiw,

With join query not possible. Use subquery. Refer below query.

CREATE TABLE #TankLine(Tank INT,Line CHAR(5),Type CHAR(5))
INSERT INTO #TankLine VALUES(1,'1A','CNT_A')
INSERT INTO #TankLine VALUES(1,'1B','CNT_A')
INSERT INTO #TankLine VALUES(1,'1C','CNT_A')
INSERT INTO #TankLine VALUES(2,'2Q','CNT_A')
INSERT INTO #TankLine VALUES(2,'2W','CNT_A')
INSERT INTO #TankLine VALUES(2,'2E','CNT_A')
INSERT INTO #TankLine VALUES(3,'3R','CNT_A')
INSERT INTO #TankLine VALUES(3,'3T','CNT_A')
INSERT INTO #TankLine VALUES(3,'3I','CNT_A')
INSERT INTO #TankLine VALUES(3,'3P','CNT_A')

CREATE TABLE #TankDetails(Tank INT,Line CHAR(5),Type CHAR(5),ID INT)
INSERT INTO #TankDetails VALUES(1,'1A','CNT_A',331)
INSERT INTO #TankDetails VALUES(1,'1B','CNT_A',311)
INSERT INTO #TankDetails VALUES(2,'2Q','CNT_A',122)
INSERT INTO #TankDetails VALUES(3,'3T','CNT_A',121)
INSERT INTO #TankDetails VALUES(3,'3I','CNT_A',144)
INSERT INTO #TankDetails VALUES(3,'3P','CNT_A',145)

CREATE TABLE #TankRemark (ID INT,Remark CHAR(5))
INSERT INTO #TankRemark VALUES(331,'Y')
INSERT INTO #TankRemark VALUES(311,'Y')
INSERT INTO #TankRemark VALUES(122,'Y')
INSERT INTO #TankRemark VALUES(121,'Y')
INSERT INTO #TankRemark VALUES(144,'Y')
INSERT INTO #TankRemark VALUES(145,'Y')

SELECT *,(SELECT TOP 1 tr.Remark FROM #TankRemark tr WHERE tr.ID = x.ID) 'Remark' FROM 
(
	SELECT DISTINCT tl.Tank,tl.Line,tl.Type,
	(SELECT TOP 1 td.ID FROM #TankDetails td WHERE tl.Tank = td.Tank) 'ID'
	FROM #TankLine tl
) x


DROP TABLE #TankRemark
DROP TABLE #TankDetails
DROP TABLE #TankLine

I agree, here is the link: https://www.e-iceblue.com/Introduce/spire-office-for-net-free.html

Join multiple tables in SQL Server

Company

Explore

Follow us

Contact Us