how to select comments for each row

Question

I have events table which include so many cols besides Id PK
and table Comments which includes text, eventId and Id PK .
how can I select the event information and it's comments in one single sql statement, how to use it and how it should look like !?

Note that EventId(Comments) = Id(events)

Answer 1

Well this should do it...

SELECT * FROM events
INNER JOIN comments on comments.eventid = events.id

If you dont need all columns it is good practice to select only the columns you realy need.

SELECT Id, eventId FROM events
INNER JOIN comments on comments.eventid = events.id

To Extract it in your C# code you might do it the System.Data style:

using (SqlConnection connection = new SqlConnection("Put your ConnectionString here"))
{
    connection.Open();
    using (SqlDataAdapter adapter = new SqlDataAdapter("My SQL from Above", connection))
    {
        DataTable table = new DataTable();
         adapter.Fill(table);
         // now you can do here what ever you like with the table...

         foreach(DataRow row in table.Rows)
         {
            if (row.IsNull("Text") == false)
               Console.WriteLine(row["Text"].ToString());
         }
    }
}

Answer 2

I would use the following statement:

SELECT e.Id, c.Id, c.text
FROM events e
INNER JOIN Comments c ON e.Id = c.EventId