Avatar of Tpaul_10
Tpaul_10
Flag for United States of America asked on

Need a SQL Query

Experts,

Please find the details as an attachment and help me writing the query.


Thanks

Query.docx
Microsoft SQL ServerMicrosoft SQL Server 2005

Avatar of undefined
Last Comment
HainKurt

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Ephraim Wangoya

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Ephraim Wangoya

correction
with cte as
(
  select a.itemnumber, b.itemdescription, a.itemcode, 
     row_number() over (partition by a.itemNumber order by b.createdatetime desc) rn
  from items a
  inner join itemsdetails b on b.itemnumber = a.itemnumber
)

select ItemNumber, ItemDescription, ItemCode
from cte
where rn = 1
and ItemDescription = 'Test2'

Open in new window

Ephraim Wangoya

oops sorry, the first one is correct
SOLUTION
Lowfatspread

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
teedo757

......I don't want to do your homework but here is some help.


you will always select what fields you want first. So if you look at your answer you have 3 fields: T1.Itemnumber, T2.Itemdescription, T1.ItemCode so you know you will want to select these.... The T1 and T2 before the field names are just explicitly telling sql to choose table1's itemnumber since both tables have Itemnumber as a field.


select T1.Itemnumber, T2.Itemdescription, T1.ItemCode from....... this is where you list what tables you are getting the data from, you only have 2 tables so it will be from table1, table 2.

Now you have the fields(select) from (your tables) where...... the last thing is to join the tables in the where clause. If you just selected the fields without a join you would get all the records which you don't want. So join them....where t1.itemnumber = t2.itemnumer and (this is where we have ItemDescription with “Test2” ) t2.Itemdescription= "Test2";


You do not need the T2.ItemDescription since it is only on table 2 however it makes it easier to find later if you need to. You will need to put it all together and if you may also need to look at the unique constraint if you get multiple results.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
HainKurt

and one more post :) if it is already posted, please ignore it...
select T1.ItemNumber,	T2.ItemDescription,	T1.ItemCode
from Table1 t1 inner join 
(
	select * from (
		select *, row_number() over(partition by itemNumber order by CreateDatetime) rn 
		from Table2
		where ItemDescription='Test2'
	) x where rn=1
) t2 on t1.itemnumber=t2.itemnumber

Open in new window

Tpaul_10

ASKER
Experts,

Sincere Thanks for the quick reply and appreciate all your help and responses.

I have tried all the queries and first one from "ewangoya" is the only query working as expected.

"ewangoya" : I am trying to modify your query to get the rows only has "test2" and couldn't get it.
The output I was looking for is = 6 Test2 AG

Thanks again for all yoru help.
Lowfatspread

for that type of query you would need to add a not exists subquery test....

if you do not tell us where/how our queries failed we cannot help.

you are now modifying your original question and technically that requires you to open a further question in  EE.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
HainKurt

I got the question wrong I guess

try this (assuming CreateDatetime is unique, ow there is no way to find the first item, somehow we should find an order, your sample does not have time part, but if they have the query will work fine)
select T1.ItemNumber,	T2.ItemDescription,	T1.ItemCode
from Table1 t1 inner join 
(
	select * from (
		select *, row_number() over(partition by itemNumber order by CreateDatetime) rn 
		from Table2
	) x where rn=1 and ItemDescription='Test2'
) t2 on t1.itemnumber=t2.itemnumber

ItemNumber	ItemDescription	ItemCode
2	Test2	AA
3	Test2	BR
4	Test2	CV
6	Test2	AG

Open in new window

SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
HainKurt

and here is the demo
with table1 as(
select 1 ItemNumber, 'AG' ItemCode
union select 2,'AA'
union select 3,'BR'
union select 4,'CV'
union select 5,'AA'
union select 6,'AG'
)
,
table2 as (
SELECT 1 ItemNumber, 'Test1' ItemDescription, '20110807 00:00:00' CreateDatetime
union select 1, 'Test2','20110807 00:00:01'
union select 1, 'Test2','20110807 00:00:02'
union select 2, 'Test2','20110808 00:00:03'
union select 2, 'Test2','20110808 00:00:04'
union select 3, 'Test2','20110809 00:00:05'
union select 3, 'Test1','20110809 00:00:06'
union select 3, 'Test2','20110809 00:00:07'
union select 4, 'Test2','20110810 00:00:08'
union select 4, 'Test2','20110810 00:00:09'
union select 5, 'Test1','20110811 00:00:10'
union select 6, 'Test2','20110812 00:00:11'
)
select T1.ItemNumber,	T2.ItemDescription,	T1.ItemCode
from Table1 t1 inner join 
(
	select * from (
		select *, row_number() over(partition by itemNumber order by CreateDatetime) rn 
		from Table2
	) x where rn=1 and ItemDescription='Test2'
) t2 on t1.itemnumber=t2.itemnumber

ItemNumber	ItemDescription	ItemCode
2	Test2	AA
3	Test2	BR
4	Test2	CV
6	Test2	AG

Open in new window