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'
LVL 2
detox1978Asked:
Who is Participating?
 
appariConnect With a Mentor Commented:
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
 
SharathConnect With a Mentor Data 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
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.