Hi lingers,

You will get all te records based on the Pid 568 using the Join query.

But PosiNo and Colour will benull for TypId 5603.

Refer below query.

SQL

DECLARE @job_cylinder AS TABLE (Id INT,PId INT,PosiNo VARCHAR(50),Colour VARCHAR(10),Direction VARCHAR(10),Circumference VARCHAR(10),Unit VARCHAR(10))
INSERT INTO @job_cylinder VALUES(5591,568,'','','','',1)
INSERT INTO @job_cylinder VALUES(5592,568,'','','','',2)
INSERT INTO @job_cylinder VALUES(5593,568,'50108102-A1','SILVER','0.5','686.823',3)
INSERT INTO @job_cylinder VALUES(5594,568,'50108111-A2','MAGENTA','0.4','686.823',4)
INSERT INTO @job_cylinder VALUES(5595,568,'50108111-3A','DEMI','0.3','686.823',5)
INSERT INTO @job_cylinder VALUES(5596,568,'50108111-4A','SPOT','0.2','686.823',6)
INSERT INTO @job_cylinder VALUES(5597,568,'50108111-5A','BLUE','0.1','686.823',7)
INSERT INTO @job_cylinder VALUES(5598,568,'50108111-6A','BLACK','0.1','686.823',8)
INSERT INTO @job_cylinder VALUES(5599,568,'50108111-7A','SEMI','0','686.823',9)
INSERT INTO @job_cylinder VALUES(5600,568,'50108102-8A','STRUCTURAL','0','686.823',10)
INSERT INTO @job_cylinder VALUES(5603,569,'50108102-A1','SILVER','0.5','686.823',3)
 
DECLARE @Stock AS TABLE (Id INT,BaseNo VARCHAR(10),TypeId INT,Active INT,PId INT)
INSERT INTO @Stock VALUES(1,'S456',5593,2,24)
INSERT INTO @Stock VALUES(2,'S278',5594,1,25)
INSERT INTO @Stock VALUES(3,'S388',5595,1,26)
INSERT INTO @Stock VALUES(4,'S312',5596,1,27)
INSERT INTO @Stock VALUES(5,'S412',5597,1,28)
INSERT INTO @Stock VALUES(6,'S489',5598,1,29)
INSERT INTO @Stock VALUES(7,'S563',5599,1,30)
INSERT INTO @Stock VALUES(8,'S101',5600,1,31)
INSERT INTO @Stock VALUES(9,'S105',5603,1,32)
 
DECLARE @Job AS TABLE (Id INT,PId INT,Description VARCHAR(10),Length INT)
INSERT INTO @Job VALUES(24,48001,'ABCDE',67)
INSERT INTO @Job VALUES(25,48002,'FGHIJ',56)
INSERT INTO @Job VALUES(26,47896,'KLMNO',63)
INSERT INTO @Job VALUES(27,49654,'PQRST',89)
INSERT INTO @Job VALUES(28,49001,'UVWXY',65)
INSERT INTO @Job VALUES(29,49002,'FXR567',62)
INSERT INTO @Job VALUES(30,49003,'HYUTRE',99)
INSERT INTO @Job VALUES(31,49004,'POHGTR',35)
INSERT INTO @Job VALUES(32,49005,'PGTBF',75)
 
SELECT j.PId, j.Description, s.BaseNo, 
(SELECT PosiNo FROM @job_cylinder jc WHERE PId = '568' AND s.TypeId = jc.Id) 'PosiNo',
(SELECT Colour FROM @job_cylinder jc WHERE PId = '568' AND s.TypeId = jc.Id) 'Colour'
FROM @Stock s
INNER JOIN @Job j ON j.Id = s.PId
WHERE s.Active = 1

Output