locked
Instruction Select using a value from a Foreach Cicle with RASOR RRS feed

  • Question

  • User-15633413 posted

    Hello,

    I'm using Visual Studio 2017 and SQL Server 2008 and I'm trying to display content form 2 tables (Topics and Catalogs - linked by a foreign key - each Topic has many catalogs). So, I tried with the bellow code but it doesn't understand "...WHERE Catalogs.id_Topic = @row.id":

    My code:

    @{
           var bd = Database.Open("IntranetConnectionString");
           string query = "SELECT id, titleTopic FROM Topics";

           var results = bd.Query(query);
           foreach (var row in results)
          {
                @row.titleTopic

               string query1 = "SELECT titleCatalog FROM Catalogs WHERE Catalogs.id_Topic = @row.id ";
               var results1 = bd.Query(query1);
               foreach (var row1 in results1)
               {
                    @row1.titleCatalog
                }
            }
    }

    I want to display every catalogs for each topic. Is's possible what I pretend to do?

    Thank you very much.

    Lina

    Monday, September 4, 2017 3:53 PM

Answers

  • User347430248 posted

    Hi LinaG,

    if you think that the above suggestion can be the answer for your question then I suggest you to mark the answer.

    it will help other community members in the future , who will have same kind of issue like yours.

    Thanks for your understanding.

    Regards

    Deepak

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, September 5, 2017 8:00 AM

All replies

  • User347430248 posted

    Hi LinaG,

    it will not work as you are trying in code above.

    if you want to fetch data from more then one table then you need to use SQL Joins.

    SQL INNER JOINSQL LEFT JOINSQL RIGHT JOINSQL FULL OUTER JOIN

    below is the example for joins.

    Inner join :

    SELECT     title, pub_name
    FROM         titles INNER JOIN
    publishers ON titles.pub_id = publishers.pub_id
    
    

    Left join:

    SELECT titles.title_id, 
           titles.title, 
           publishers.pub_name
    FROM titles LEFT OUTER JOIN publishers 
                ON titles.pub_id 
                 = publishers.pub_id
    
    

    Right join:

    SELECT titles.title_id, 
           titles.title, 
           publishers.pub_name
    FROM titles RIGHT OUTER JOIN publishers 
                ON titles.pub_id 
                 = publishers.pub_id
    
    

    Full outer join:

    SELECT titles.title_id, 
           titles.title, 
           publishers.pub_name
    FROM titles FULL OUTER JOIN publishers 
                ON titles.pub_id 
                 = publishers.pub_id
    
    

    Reference:

    1. Types of Joins
    2. SQL Joins
    3. T-SQL Join Types

    so first you need to make a query with the use of joins and try to test it in SQL Server. once it working correctly then you can try to modify your code and use the query with in your code.

    Regards

    Deepak

    Tuesday, September 5, 2017 1:13 AM
  • User-15633413 posted

    Ok. Thank you

    Tuesday, September 5, 2017 7:58 AM
  • User347430248 posted

    Hi LinaG,

    if you think that the above suggestion can be the answer for your question then I suggest you to mark the answer.

    it will help other community members in the future , who will have same kind of issue like yours.

    Thanks for your understanding.

    Regards

    Deepak

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, September 5, 2017 8:00 AM