TSQL: SELECT data based on another SELECT Statement

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

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

ASKER

Actually both work, but filter the results for best after they are returned.

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

I guess ID is unique in both tables.

ASKER

ID is unique in MyTable, but has multiples (or none) in MySecondTable

ASKER

I think there is an extra ) in the last snippet you posted

Msg 102, Level 15, State 1, Line 4
Incorrect syntax near ')'.

appari

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

appari

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

ASKER

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

ASKER

Msg 4145, Level 15, State 1, Line 5
An expression of non-boolean type specified in a context where a condition is expected, near ')'.

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

ASKER

I still get an error. Upped to 500 points....

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

Do you have ID column in MyTable?

ASKER

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

ASKER

ignore post above.

yes there is a column called ID

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)

ASKER

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

can you post the column names of MyTable and MySecondTable