"checking" for a null field

Results 1 to 2 of 2

Thread: "checking" for a null field

  1. #1
    Join Date
    Dec 1969

    Default "checking" for a null field

    well, thanks to CoolDude and BW for all the advice - it&#039;s been a great help. with the sql statement the way it is now, i&#039;ve eliminated the need to have a separate "totalStats" table to contain all the totalStat info, which is great. I&#039;ve also been able to eliminate the need for multiple IIF statements for point totals in each class.<BR><BR>Now I&#039;m left with checking for "null" fields sorta speak. if i&#039;m displaying all the classes for all the users and UserA has 10 points in class1, but UserB has 0 points (or no records), I still want class1 pts for UserB to show up with a 0. Also, if the user finished in a position where they didn&#039;t get any points (but there is a record for them in the records table), I would want that to show. So I now would want to check for null values wouldn&#039;t I? What would be the best way to do this?<BR><BR>thanks,<BR><BR>ed<BR>

  2. #2
    Join Date
    Dec 1969

    Default LEFT JOIN

    SELECT ...<BR>FROM users LEFT JOIN records<BR>ON records.id = users.id<BR>GROUP BY ...<BR>ORDER BY ...<BR><BR>LEFT JOIN means "take all the records from the left side, even if there aren&#039;t any matches on the right side." (Betcha can&#039;t guess what RIGHT JOIN means?)<BR><BR>Note that this will give you NULL, *NOT* zero, when there is a left side record with no right side match, so you need to check for NULLs in your VBS code.<BR><BR>The easiest way to do that:<BR><BR>&#060;%<BR>Set RS = ...<BR>Do Until RS.EOF<BR> val = "" & RS("maybeNullField")<BR> If val = "" Then<BR> val = 0 &#039; or whatever you want the default to be<BR> End If<BR> Response.Write val<BR> ...<BR> RS.MoveNext<BR>Loop<BR>%&#062;<BR><BR>

Posting Permissions

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