[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 841
  • Last Modified:

TSQL: SELECT data based on another SELECT Statement

I have a select satement below;

select * from
(select *, row_number() over(order by [ID]) seq from [MyDatabase].[dbo].[MyTable]) x
where seq between 16 and 30

How do i filter the results above so that only ID are displayed that are also in the statment below?

SELECT [ID] FROM [MyDatabase].[dbo].[MySecondTable]) Where [best]='5'
0
detox1978
Asked:
detox1978
  • 11
  • 7
  • 3
2 Solutions
 
appariCommented:
try this

select * from
(select *, row_number() over(order by [ID]) seq from [MyDatabase].[dbo].[MyTable]) x
join  [MyDatabase].[dbo].[MySecondTable] A
on A.[best] ='5'
and A.[ID} = x.[ID]
where seq between 16 and 30
0
 
SharathData EngineerCommented:
you can also try like this.
select * from
(select *, row_number() over(order by [ID]) seq from [MyDatabase].[dbo].[MyTable]) x
where seq between 16 and 30
  and ID in (select ID from [MyDatabase].[dbo].[MySecondTable] y where x.ID = y.ID and y.[best] = '5')

Open in new window

0
 
detox1978Author Commented:
Sharath_123 your's works, but i've made a mistake.


Currently it returns row 16 and 30 and filters them for best=5.


I need it to return all rows that are best=5 and filter the rows 16 to 30 fromt the results
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
detox1978Author Commented:
Actually both work, but filter the results for best after they are returned.
0
 
SharathData EngineerCommented:
check this one

select * from (
SELECT x.*,row_number() over(order by [x.ID]) seq
  from [MyDatabase].[dbo].[MyTable]) x
  JOIN [MyDatabase].[dbo].[MySecondTable] y on x.ID = y.ID 
 where y.[best] = '5')
where seq between 16 and 30

Open in new window

0
 
SharathData EngineerCommented:
I guess ID is unique in both tables.
0
 
detox1978Author Commented:
ID is unique in MyTable, but has multiples (or none)  in MySecondTable
0
 
detox1978Author Commented:
I think there is an extra ) in the last snippet you posted

Msg 102, Level 15, State 1, Line 4
Incorrect syntax near ')'.
0
 
appariCommented:
try this

select * from
(select *, row_number() over(order by [ID]) seq from [MyDatabase].[dbo].[MyTable] A
join  [MyDatabase].[dbo].[MySecondTable] B
on B.[best] ='5'
and A.[ID} = B.[ID]) X
where seq between 16 and 30
0
 
appariCommented:
previous one may give error try this

select * from
(select *, row_number() over(order by A.[ID]) seq from [MyDatabase].[dbo].[MyTable] A
join  [MyDatabase].[dbo].[MySecondTable] B
on B.[best] ='5'
and A.[ID} = B.[ID]) X
where seq between 16 and 30
0
 
detox1978Author Commented:
The error has now moved to line 5
select * from
(select *, row_number() over(order by [ID]) seq from [MyDatabase].[dbo].[MyTable] A
join  [MyDatabase].[dbo].[MySecondTable] B
on B.[best] ='5'
and A.[ID} = B.[ID]) X
where seq between 16 and 30

Open in new window

0
 
detox1978Author Commented:
Msg 4145, Level 15, State 1, Line 5
An expression of non-boolean type specified in a context where a condition is expected, near ')'.
0
 
SharathData EngineerCommented:
check this one
select * from (
SELECT x.*,row_number() over(order by [x.ID]) seq
  from [MyDatabase].[dbo].[MyTable] x
  JOIN [MyDatabase].[dbo].[MySecondTable] y on x.ID = y.ID 
 where y.[best] = '5') t1
where seq between 16 and 30

Open in new window

0
 
detox1978Author Commented:
I still get an error.  Upped to 500 points....
Msg 207, Level 16, State 1, Line 2
Invalid column name 'x.ID'.

Open in new window

0
 
SharathData EngineerCommented:
Do you have ID column in MyTable?
0
 
detox1978Author Commented:
Sorry that should have said;

Msg 207, Level 16, State 1, Line 2
Invalid column name 'x.QuestionID'.


and yes there is a column called QuestionID
0
 
detox1978Author Commented:
ignore post above.


yes there is a column called ID
0
 
SharathData EngineerCommented:
I didn't use QuestionID in my query. then how can you get that error? Can  you post your exact error message for my query (Post ID: 23666666)
0
 
detox1978Author Commented:
Msg 207, Level 16, State 1, Line 2
Invalid column name 'x.ID'.
0
 
SharathData EngineerCommented:
can you post the column names of MyTable and MySecondTable
0
 
detox1978Author Commented:
Sorted it myself, thanks for the pointers...
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 11
  • 7
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now