locked
Help with Select statement RRS feed

  • Question

  • User-371148474 posted

    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

    Wednesday, October 29, 2014 10:15 AM

Answers

  • User-821857111 posted
    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);



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, October 29, 2014 10:58 AM

All replies

  • User-821857111 posted

    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

    Wednesday, October 29, 2014 10:20 AM
  • User-371148474 posted

    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

    Wednesday, October 29, 2014 10:35 AM
  • User-821857111 posted
    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);



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, October 29, 2014 10:58 AM