returning table name when all fields NULL

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

returning table name when all fields NULL

Author

Topic

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 2002-09-26 : 11:44:11

I have records in various tables that consist of a string of NULLs. This string of NULLs indicates that the specific person attached to the record has not completed a specific test.

I'd like to run a query that shows me which tests a student has not completed. I have one table per unique test. My plan is to run the SELECT statement against each table and then UNION them all together to get the results. The tricky part for me is returning the TABLE NAME (thus, the test name), which describes the missed test.

Something like this:

SELECT studentID.History_Test FROM History_Test
WHERE [all fields are NULL]
[and return Actual SQL TABLENAME as MissedTest]

UNION

Next table...

results would look like this:

studentID/MissedTest
1234/History_Test

thx

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-09-26 : 12:27:15

This might work:

SELECT 'HistoryTest' AS MissedTest, StudentID
FROM HistoryTest
WHERE Coalesce(col1, col2, col3, col4) IS NULL
UNION
SELECT 'MathTest' AS MissedTest, StudentID
FROM MathTest
WHERE Coalesce(col1, col2, col3, col4) IS NULL
UNION
...

Of course, you realize we're gonna ask why you have tables set up that way (I kinda already know why, but I love hearing it anyway!) and why you can't consolidate them into a single table (ditto!)

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 2002-09-26 : 12:49:42

rob, thanks. Here's what I came up with before I saw your answer:

SELECT history_test.studentid,
sysobjects.name AS TableName
FROM history_test, sysobjects
WHERE COALESCE (col1, col2) IS NULL AND
sysobjects.name = 'history_test'

Your code is more elegant since it does away with the need to call the sysobjects table. Oops, missed that ;-), I'm embarrassed.

Re: why the single tables? They're VIEWS of the larger table and I'm using them b/c they enable me to UNION together all results this way:

studentid/missed_test

1234/history_test
1234/math_test
2345/history_test
2345/english_test

Considering how I missed the obvious the first time around, I suppose it's conceivable I'm missing how to do the above by running a query against the base table. If you'd like to enlighten me again, I won't complain.

Cheers!

thx again.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-09-26 : 13:17:02

Wow, OK, now I ABSOLUTELY HAVE TO ASK THIS: if the test data is already in one single table, why aren't you querying that table instead of trying to UNION multiple views together? How do the views partition out each test type (history, math, etc.)? Can you post the structure of the base table?

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 2002-09-26 : 13:22:02

OK,

Here's the deal. The views are in place temporarily via Access link tables so the people who use the data can look at unique test results by test subject. Eventually, when I move to a web-based data entry/management interface (someday!), I will simply present these features via the web browser.

I have no good answer to your question as to why I'm not querying the base table directly. I'm slow off the mark today, been staring at way too many VIEWS, I guess. Now I see the light.

thx.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources