CODEWITHSUNDEEP
sql-serveroracle-database
John
John

Reputation: 1

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

Hi All I am new in SQL need your help in writing below query

I have below Tables

table1

code        ItemNo

A           12345
A           12346
A           12347
A           12348
B           12349
B           12350
B           12351
B           12352
C           12343
C           12354
C           12355
C           12356

table2

ItemNo          Value

12345           S
12346           S
12347           I
12348           B
12349           I
12350           S
12351           S
12352           S
12353           S
12354           S
12355           S
12356           S

Now I need to get the code from table1 by passing ItemNo in where clause based on that i'll get Code. Than i need to fetch all the ItemNo which are associated with that Code and if code is having Value=S only not other value than print it's ItemNo and code.

Upvotes: 0

Views: 37

Answers (3)

Brian Mayer
Brian Mayer

Reputation: 36

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

Upvotes: 0

Suraj Kumar
Suraj Kumar

Reputation: 5643

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

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

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')

Upvotes: 1

Related Questions