locked
Detecting empty row in table RRS feed

  • Question

  • User1771308999 posted

    Is there a way to detect an empty row in a table? 

    For example, I have three survey form - thus three tables for each survey.

    A user completed two survey. So two tables are populated but not the third.

    How do I detect if the third table (and that row for that specific user) is empty?

    Tuesday, August 20, 2013 12:38 PM

Answers

  • User-821857111 posted

    You can use a LEFT JOIN to detect if there are no matching records across a relationship:

    SELECT SurveyTable1.ID, SurveyTable2.ID, SurveyTable3.ID
    FROM RecordTable 
    LEFT JOIN SurveyTable1 ON RecordTable.UserID = SurveyTable1.UserId 
    LEFT JOIN SurveyTable2 ON RecordTable.UserID = SurveyTable2.UserId 
    LEFT JOIN SurveyTable3 ON RecordTable.UserID = SurveyTable3.UserID;



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, August 21, 2013 2:46 PM

All replies

  • User895691971 posted

    In your code you can do something like: 

    foreach (var row in result) { 
     if(row.RowName == "") { // instead of "" you can use null, or " "; to check white space
      // write something..
     }
    }

    Tuesday, August 20, 2013 1:22 PM
  • User281315223 posted

    This ultimately depends on your structure / objects that you are using, but if you are simply storing a string for each of the rows, you should be able to determine if a row is empty or not by using the String.IsNullOrEmpty() method

    //If your row is null or empty - this will be true (indicating it is empty)
    if(String.IsNullOrEmpty(YourRow.Text))
    {
          //Handle logic for an empty row
    }

    Do you have any examples of the code that you are currently using? It maybe helpful for providing a more detailed answer.

    Tuesday, August 20, 2013 1:31 PM
  • User-821857111 posted

    Can you provide more info on your database schema? Also, will the third table have a row for the user regardless whether they completed the third survey?

    Tuesday, August 20, 2013 1:46 PM
  • User1771308999 posted

    What I did for my survey:

    I have four tables:

    The first table contain a list of all users for my website. It also contain three columns, one for each survey (a total of three survey). Each are set to NULL by default. This help me identify whether or not the user had completed a survey. We can tall that the Record Table.

    The next three tables are empty and will be populated with data when a user completed a survey and click the submit button. We can call that Survey Table 1, Survey Table 2, and Survey Table 3.

    What happens is, when a user complete the first survey and click submit, a couple things happens: the Record Table get updated. It identify the user by it ID, then update the first column for the first survey by changing from NULL to 1. Then Survey Table 1 get a new row containing the data from the first survey that is cross-referenced with the same user's ID.

    So when I create a Result Page that display each user's data from the survey - I will be able to display the data from the first survey by using Survey Table 1, seachable by the user's ID. However, Survey Table 2, and Survey Table 3 will still be empty. I am looking for a way to detect exactly that -- Over time, numerous of users will complete the survey.

    Suppose Survey Table 1 have 5 rows, each with unique user data from the first survey. However, Survey Table 2 will have only 4 rows. As one user completed the first survey but not the second survey.

    What I want to do is -- I search Survey Table 2 and detected that there's no row for that user that exist. Thus an empty row -- in another word, no record found.

    I hope this make sense? Please let me know and I'll try to be more clear! I was constantly in and out of the office all day yesterday. Hopefully, I'll be more attentive today! Thank you!!

    Wednesday, August 21, 2013 8:40 AM
  • User895691971 posted

    Let me provide you a good idea. Your idea is a good one, you need to check that user went through these steps, if value is 1 he complete the survey if not then he has not!

    Lets make it short.

    Suppose you are having ONE table only named as Survey, there are 15 columns, each column being followed by a column names "survery_1", "survery_2". If the value for them is 1 you then get the value from there, if the value for this is 0 then you can show that the survey has not been taken, and the value should be saved in front of these columns like 

    survery_1 opt_1 opt_2 survery_2 opt_1 opt_2 opt_3 ...

    But make sure the column names are not alike. 

    Wednesday, August 21, 2013 12:04 PM
  • User-821857111 posted

    You can use a LEFT JOIN to detect if there are no matching records across a relationship:

    SELECT SurveyTable1.ID, SurveyTable2.ID, SurveyTable3.ID
    FROM RecordTable 
    LEFT JOIN SurveyTable1 ON RecordTable.UserID = SurveyTable1.UserId 
    LEFT JOIN SurveyTable2 ON RecordTable.UserID = SurveyTable2.UserId 
    LEFT JOIN SurveyTable3 ON RecordTable.UserID = SurveyTable3.UserID;



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, August 21, 2013 2:46 PM
  • User895691971 posted

    Is it legal to use more than one join? 

    Wednesday, August 21, 2013 3:24 PM
  • User-821857111 posted

    Is it legal to use more than one join? 

    Yep. There is theoretically no limit to the number of tables you can include in a query except what the system's resources allow (Sql Server 2008 onwards - previous to that the limit was 256 tables in 2005).

    Wednesday, August 21, 2013 4:22 PM
  • User895691971 posted

    Afzaal.Ahmad.Zeeshan

    Is it legal to use more than one join? 

    Yep. There is theoretically no limit to the number of tables you can include in a query except what the system's resources allow (Sql Server 2008 onwards - previous to that the limit was 256 tables in 2005).

    256 Tables in one Query! O.o 

    However, thanks alot! I will try using this in my app too. 

    Thursday, August 22, 2013 3:49 AM