Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2011
    Posts
    1

    Unanswered: Displaying data on a form

    I have a database with the following tables (shortened):
    table1 (record, first name, last name, notes)
    table2 (id, record, todo)

    Data in tables (example):
    table1
    1, john, doe, testing
    2, jane, doe, testing

    table2
    1, 1, action1
    2, 1, action2
    3, 2, action1
    4, 2, action2

    On my ASP.NET webpage, I bind to a data source that selects from table1.
    This results in one query that pulls all the rows. However, I would like to include as a column to display a list of todo actions for each person. In the above example, there would be 2 items for john and 2 for jane.

    If I make these separate data sources, then it is another extra query for EACH person in table1, which is really inefficient on large datasets. I could make the query if they click on a person for more details, but I need it where it is all pre-populated and shown in the same table on the first load. Are there queries or design methods that handle this problem? I'm still pretty new to databases.

    Thanks.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    With SQL Server 2005+, you can use Common Table Expressions (CTEs) to create comma delimited strings in a single recursive query.
    Prior to version 2005, this could be done using a user-defined function.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •