Need Urgen Help: INNER JOIN, - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Need Urgen Help: INNER JOIN,

Author

Topic

mms
Starting Member

2 Posts

Posted - 2008-03-15 : 13:44:01

hi,
Please some one tell me how to write query for this scenario.
I have table Books_Category
which have fields
CategoryID
BOOKID

Now I want to search all those record which must have two Category(1 and 2)
Table is look like

BookID --- CategoryID
--------------------------
1 --------- 1
1 --------- 2
2 --------- 3
2 --------- 4
3 --------- 2
3 --------- 1
Now I want to search ALL those records which must have Category 1 & 2
I need to write in Stored Procedure.
I hade try one, but having Error: Column was specified multiple times

With AllBooks as (
SELECT ROW_NUMBER() OVER (ORDER BY ID ASC) as Row, *
FROM Book INNER JOIN
Books_Category ON Book.ID = Books_Category.BookID INNER JOIN
Books_Category AS Books_Category_1 ON Book.ID = Books_Category_1.BookID
WHERE (Books_Category.categoryID = 1) AND (Movie_Genres_1.GenreID = 2)
)

Select *
FROM AllBooks
WHERE Row between
@startRowIndex and @StartRowIndex+@NumRows-1;

Please tell me how can I handle it in StoreProcedure
Thanks.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-03-15 : 14:15:14

replace * with column names in the cte.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

mms
Starting Member

2 Posts

Posted - 2008-03-15 : 15:44:26

Thanks alot,
Problem is solved....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources