SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Wizard bbolte's Avatar
    Join Date
    Nov 2001
    Location
    The Central Plains
    Posts
    3,304
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question SQL statement help

    Ok, hypothetical (this is actually for a class i'm taking). You have a db with 6 tables, each have several columns. The boss wants you to check for nulls in each table. How would you do it? Write a different statement for each table with a big long "WHERE fieldname IS NULL" for each column? Or is there an easier, simpler and quicker way to do it? Anyone have any bones to throw a newbie here?

  2. #2
    Are you ready for BSD? Marshall's Avatar
    Join Date
    Dec 2001
    Posts
    373
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Write a different statement for each table with a big long "WHERE fieldname IS NULL" for each column?
    This is the way that comes immediately to mind. Are we limited to using SQL? Most databases also provide a programming API if you prefer a more direct approach. And, of course, you can always edit the database files yourself.

    The smart solution, however, is to not allow nulls in the table to begin with. Then you never have to check :-)

    - Marshall

  3. #3
    SitePoint Wizard silver trophy Jeremy W.'s Avatar
    Join Date
    Jun 2001
    Location
    Toronto, Canada
    Posts
    9,123
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    SQL provides a means to do crosstable checks, and provides a means to check for null... Can't you just combine the 2 marshall? Be a massive SQL statement, but still...
    SVP Marketing, SoCast SRM
    Personal blog: Strategerize
    Twitter: @jeremywright

  4. #4
    SitePoint Wizard bbolte's Avatar
    Join Date
    Nov 2001
    Location
    The Central Plains
    Posts
    3,304
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes, we are limited to SQL, since that is the class I'm taking...

    And, I didn't build the dbs, they were given to me and told to check for nulls...

  5. #5
    Are you ready for BSD? Marshall's Avatar
    Join Date
    Dec 2001
    Posts
    373
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The problem with using JOINs to combine NULL records from various tables would be knowing where the NULL record originated from, if such information is necessary for additional operations. You'd need to develop some sort of table-unique identifier scheme -- maybe IDs in each table that are prepended with the table name, such as "MyTable_12345". Could work :-)

    - Marshall

  6. #6
    SitePoint Wizard bbolte's Avatar
    Join Date
    Nov 2001
    Location
    The Central Plains
    Posts
    3,304
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Marshall - uhmm, what?

  7. #7
    SitePoint Wizard
    Join Date
    Jan 2001
    Location
    Milton Keynes, UK
    Posts
    1,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What database are we talking about here (eg SQL Server, Sybase, mySQL etc)?

    Also, what type of report is needed?
    1) Total number of nulls in each field for each table 2) Total number of nulls in a table
    3) Total number of nulls in all tables
    ...


    I don't see that 2 or 3 would be any use though.
    Last edited by shane; Feb 4, 2002 at 08:35.

  8. #8
    SitePoint Wizard silver trophy Jeremy W.'s Avatar
    Join Date
    Jun 2001
    Location
    Toronto, Canada
    Posts
    9,123
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Marshall... very true that... then you'd need a whole host of ASL or PHP to do the distinguishing and sorting anyways... Might as well just do it with some smart ASP/PHP as you can return results that way
    SVP Marketing, SoCast SRM
    Personal blog: Strategerize
    Twitter: @jeremywright

  9. #9
    SitePoint Wizard
    Join Date
    Jan 2001
    Location
    Milton Keynes, UK
    Posts
    1,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    But why would you join the tables anyway?

  10. #10
    SitePoint Wizard bbolte's Avatar
    Join Date
    Nov 2001
    Location
    The Central Plains
    Posts
    3,304
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by shane
    What database are we talking about here (eg SQL Server, Sybase, mySQL etc)?
    for the class, we're simply using access, but would be curious how to do it for sql server. and i need to show total null fields in each table.

  11. #11
    Are you ready for BSD? Marshall's Avatar
    Join Date
    Dec 2001
    Posts
    373
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    But why would you join the tables anyway?
    Jeremy asked why we couldn't just combine everything into a single SQL statement. I was answering his question :-)


Bookmarks

Posting Permissions

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