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
