[RESOLVED]Help with Select statement

HI,

This is my third project with Razor syntax.

I was able to allow a user subscribe to some categories of articles to get feeds from them.. The feeds are in another table while the subscribed categories are in another table

The subscription table have multiple rows with the same userid but different categoryid…

How do I write a join Select statement to select the feeds from the article feeds table to join with the subscribed categories by a user in the subscription table..

Remember I have to present this result using the current user_id… I don’t know if the WHERE clause should include the categories_id too

Thanks

That are the table and column names of the relevant tables? It’s a bit difficult writing SQL for you unless you provide that information

OK..

Subscription table

id     UserID      CategoryID

1         1                  10

2          1                   3

3          1                  5

Articles feed table

Id         ArticleTitle ArticleDesc   NewsDate    Category   ContentUser URL 

1           xxxxxxx          xxxxx         2012-09-03     10                6              xxxx

2           xxxxxxx          xxxxx         2013-09-03     3                8              xxxx

3           xxxxxxx          xxxxx         2012-09-03     5                6              xxxx

This  is an assumed design of the table… But this should suffice enough

var sql = "SELECT Feeds.* From Feeds INNER JOIN Subscriptions ON Feeds.CategoryID = Subscriptions.CategoryID WHERE Subscriptions.UserID = @0";
var data = db.Query(sql, WebSecurity.CurrentUserId);

Leave a Reply