How to get data based from multiple tables based on specific column value

I think you need not exists :

with cte as (
     select t1.code, t1.itemno, t2.value
     from table1 t1 inner join
          table2 t2
          on t1.itemno = t2.itemno
)
select c.*
from cte c
where not exists (select 1 from cte c1 where c1.code = c.code and c1.value <> 'S')

You can try this

Select table1.ItemNo, table1.Code, table2.Value from table1
inner join table2 on table1.ItemNo = table2.ItemNo
where table1.ItemNo = '12345'

You can learn this SQL Server Join

Being new to SQL means you will need to understand table joins and a subquery. I will take it one step at a time, hope it helps.

SQL to create and populate the tables:

IF OBJECT_ID('tempdb..#tmpTable1') IS NOT NULL
  /*Then it exists*/
  DROP TABLE #tmpTable1

CREATE TABLE #tmpTable1 (
    code varchar(4) not null,
    ItemNo smallint not null )

insert into #tmpTable1 VALUES('A', 12345)
insert into #tmpTable1 VALUES('A', 12346)
insert into #tmpTable1 VALUES('A', 12347)
insert into #tmpTable1 VALUES('A', 12348)
insert into #tmpTable1 VALUES('B', 12349)
insert into #tmpTable1 VALUES('B', 12350)
insert into #tmpTable1 VALUES('B', 12351)
insert into #tmpTable1 VALUES('B', 12352)
insert into #tmpTable1 VALUES('C', 12353)
insert into #tmpTable1 VALUES('C', 12354)
insert into #tmpTable1 VALUES('C', 12355)
insert into #tmpTable1 VALUES('C', 12356)

IF OBJECT_ID('tempdb..#tmpTable2') IS NOT NULL
  /*Then it exists*/
  DROP TABLE #tmpTable2

CREATE TABLE #tmpTable2 (
    ItemNo smallint not null,
    Value varchar(4) not null )

insert into #tmpTable2 VALUES(12345, 'S')
insert into #tmpTable2 VALUES(12346, 'S')
insert into #tmpTable2 VALUES(12347, 'I')
insert into #tmpTable2 VALUES(12348, 'B')
insert into #tmpTable2 VALUES(12349, 'I')
insert into #tmpTable2 VALUES(12350, 'S')
insert into #tmpTable2 VALUES(12351, 'S')
insert into #tmpTable2 VALUES(12352, 'S')
insert into #tmpTable2 VALUES(12353, 'S')
insert into #tmpTable2 VALUES(12354, 'S')
insert into #tmpTable2 VALUES(12355, 'S')
insert into #tmpTable2 VALUES(12356, 'S')

SQL for your first condition, "get the code from table1 by passing ItemNo in where clause" select t1.code from #tmpTable1 t1 where t1.ItemNo = 12350

Result: code B

SQL to add your second condition, "fetch all the ItemNo which are associated with that Code" Use the original query as a subquery.

select t1.ItemNo from #tmpTable1 t1 
where t1.code = (
    select t1.code from #tmpTable1 t1 
    where t1.ItemNo = 12350 )

Result: ItemNo 12349 12350 12351 12352

SQL to add your second condition "and if code is having Value=S only" Join to Table2, add 'S' to where. "print it's ItemNo and code", add Code to the select

select t1.ItemNo, t1.code from #tmpTable1 t1 
inner join #tmpTable2 t2 on t2.ItemNo = t1.ItemNo
where t1.code = (
    select t1.code from #tmpTable1 t1 
    where t1.ItemNo = 12350 )
and t2.Value = 'S'

Result: ItemNo code 12350 B 12351 B 12352 B