Hi lingers,

You have to use below query to select multiple PosiNo.

SELECT Id,PosiNo FROM job_cylinder WHERE PosiNo IN (SELECT PosiNo FROM job_cylinder WHERE PId = '568') AND PosiNo <> '' ORDER BY unit DESC

Then use the result to save in Session.

Query

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)

SELECT Id,PosiNo FROM @job_cylinder WHERE PosiNo IN (SELECT PosiNo FROM @job_cylinder WHERE PId = '568') AND PosiNo <> '' ORDER BY unit DESC

Code

string selectSQL1;
selectSQL1 = "SELECT Id,PosiNo FROM job_cylinder WHERE PosiNo IN (SELECT PosiNo FROM job_cylinder WHERE PId = '568') AND PosiNo <> '' ORDER BY unit DESC";
dbConn2.ConnectionString = "Data Source=NERE\\SQLEXPRESS01; Initial Catalog=kaging;Integrated Security=True;";
cmd2.Connection = dbConn2;
cmd2.CommandText = selectSQL1;
cmd2.CommandType = CommandType.Text;
dbConn2.Open();
dr2 = cmd2.ExecuteReader();
List<string> posinos = new List<string>();
while (dr2.Read())
{
    posinos.Add(dr2["posino"].ToString());
}
Session["hj"] = posinos;
dbConn2.Close();

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, js.Id,js.PosiNo,js.Colour
FROM @Stock s
INNER JOIN @Job j ON j.Id = s.PId
INNER JOIN (SELECT Id,PosiNo,Colour FROM @job_cylinder WHERE PosiNo IN (SELECT PosiNo FROM @job_cylinder WHERE PId = '568') AND PosiNo <> '') js ON js.Id = s.TypeId

Output