SQL statement help

[bbolte]

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?

[Marshall]

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

[Jeremy W.]

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...

[bbolte]

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...

[Marshall]

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

[bbolte]

Marshall - uhmm, what?

[shane]

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.

[Jeremy W.]

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

[shane]

But why would you join the tables anyway?

[bbolte]

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.

[Marshall]

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 :-)