SitePoint Sponsor |
|
User Tag List
Results 1 to 11 of 11
Thread: SQL statement help
-
Feb 3, 2002, 22:56 #1
- Join Date
- Nov 2001
- Location
- The Central Plains
- Posts
- 3,304
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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?
-
Feb 3, 2002, 23:03 #2Write a different statement for each table with a big long "WHERE fieldname IS NULL" for each column?
The smart solution, however, is to not allow nulls in the table to begin with. Then you never have to check :-)
- Marshall
-
Feb 3, 2002, 23:05 #3
- 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...
-
Feb 3, 2002, 23:20 #4
- 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...
-
Feb 3, 2002, 23:27 #5
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
-
Feb 3, 2002, 23:33 #6
- Join Date
- Nov 2001
- Location
- The Central Plains
- Posts
- 3,304
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Marshall - uhmm, what?
-
Feb 4, 2002, 08:31 #7
- 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.
-
Feb 4, 2002, 08:33 #8
- 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
-
Feb 4, 2002, 08:39 #9
- 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?
-
Feb 4, 2002, 08:59 #10
- 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)?
-
Feb 4, 2002, 15:20 #11But why would you join the tables anyway?
Bookmarks