How not to return NULL when no data exist - mitr

Hi All,

With help from this newsgroup I got the sql query shown below to return rows
as column.
Now, I need to make a minor modification to the sql query to NOT return rows
with NULL value when no data was found for MimeDate and/or MimeSubject,
and/or MimeMessageID.  When I added a WHERE clause, WHERE hv.sContent is not
NULL, then no rows were returned.

Please note data for the MimeDate, the MimeSubject, and the MimeMessageID
are all retrieved from the same column, hv.sContent.

SELECT fm.nMessage,fm.nReview, fm.dReviewed
MIN(CASE WHEN h.nHeaderID = 8 THEN hv.sContent END) AS MimeDate,
MIN(CASE WHEN h.nHeaderID = 7 THEN hv.sContent END) AS MimeSubject,
MIN(CASE WHEN h.nHeaderID = 9 THEN hv.sContent END) AS MimeMessageID
FROM T_Header AS h
JOIN T_LU_Header_Value hv ON h.nHeaderValueID = hv.nID
JOIN T_FLAG_MESSAGE fm ON fm.nMessage = h.nMessageID
Group by fm.nMessage
ORDER by fm.nMessage

Thank you!

--
Mitra

How not to return NULL when no data exist - Tom Cooper

Here are two possible ways, one with a derived table (this would be my
preferred way)

SELECT x.nMessage, x.nReview, x.dReviewed,
x.MimeDate, x.MimeSubject, x.MimeMessageID
FROM (SELECT fm.nMessage,fm.nReview, fm.dReviewed,
MIN(CASE WHEN h.nHeaderID = 8 THEN hv.sContent END) AS MimeDate,
MIN(CASE WHEN h.nHeaderID = 7 THEN hv.sContent END) AS MimeSubject,
MIN(CASE WHEN h.nHeaderID = 9 THEN hv.sContent END) AS MimeMessageID
FROM T_Header AS h
JOIN T_LU_Header_Value hv ON h.nHeaderValueID = hv.nID
JOIN T_FLAG_MESSAGE fm ON fm.nMessage = h.nMessageID
Group by fm.nMessage) AS x
WHERE x.MimeDate IS NOT NULL AND
x.MimeSubject IS NOT NULL AND
x.MimeMessageID IS NOT NULL
ORDER by x.nMessage

Or (I don't like this as well because you have to repeat the calculations
for the Mime columns)

SELECT fm.nMessage,fm.nReview, fm.dReviewed,
MIN(CASE WHEN h.nHeaderID = 8 THEN hv.sContent END) AS MimeDate,
MIN(CASE WHEN h.nHeaderID = 7 THEN hv.sContent END) AS MimeSubject,
MIN(CASE WHEN h.nHeaderID = 9 THEN hv.sContent END) AS MimeMessageID
FROM T_Header AS h
JOIN T_LU_Header_Value hv ON h.nHeaderValueID = hv.nID
JOIN T_FLAG_MESSAGE fm ON fm.nMessage = h.nMessageID
Group by fm.nMessage
HAVING MIN(CASE WHEN h.nHeaderID = 8 THEN hv.sContent END)  IS NOT NULL AND
MIN(CASE WHEN h.nHeaderID = 7 THEN hv.sContent END) IS NOT NULL AND
MIN(CASE WHEN h.nHeaderID = 9 THEN hv.sContent END) IS NOT NULL
ORDER by fm.nMessage

Tom

How not to return NULL when no data exist - TheSQLGuru

I am quite interested in the performance of those two queries.  Mitra, can
you please execute both of them and provide us with the actual query cost
and the I/Os performed for each?  Thanks bunches in advance!

--
TheSQLGuru
President
Indicium Resources, Inc.

How not to return NULL when no data exist - mitr

Thank you Tom!
--
Mitra

How not to return NULL when no data exist - mitr

Hi "TheSQLGuru",

I needed the sql query for extracting data from MySQL and importing to MSSQL
Server 2000.
I am new to MySQL and not sure how to get the actual query cost and the I/Os
performed for each of those queries. However, I did find this menu option:
gives you some insight about the performance difference of the two queries.

The "Explain" for the query with derived tables is as follow and please note
the first line is the column header:

1, 'PRIMARY', '<derived2>', 'ALL', '', '', '', '', 10904, 'Using where;
Using filesort'
2, 'DERIVED', 'fm', 'ALL', 'idx_nMessage', '', '', '', 10904, 'Using
temporary; Using filesort'
2, 'DERIVED', 'h', 'ref',
'idx_nMessageID,idx_nMessageID_nHeaderID_nHeaderValueID', 'idx_nMessageID',
'4', 'mngmt.fm.nMessage', 466, ''
2, 'DERIVED', 'hv', 'eq_ref', 'PRIMARY', 'PRIMARY', '4',
'mngmt.h.nHeaderValueID', 1, ''

The "Explain" for the query with HAVING clause is:

1, 'SIMPLE', 'fm', 'ALL', 'idx_nMessage', '', '', '', 10904, 'Using
temporary; Using filesort'
1, 'SIMPLE', 'h', 'ref',
'idx_nMessageID,idx_nMessageID_nHeaderID_nHeaderValueID', 'idx_nMessageID',
'4', 'mngmt.fm.nMessage', 466,
1, 'SIMPLE', 'hv', 'eq_ref', 'PRIMARY', 'PRIMARY', '4',
'mngmt.h.nHeaderValueID', 1, ''''

I ran both queries three times and the here are the Exection times in MySQL
Browser:

The Execution time for the SQL query with derived tables:
0.1676s, 0.1607s, 0.1736s

The Execution time for the SQL query with HAVING clause:
0.1647s, 0.1640s, 1.1697s

--
Mitra

How not to return NULL when no data exist - mitr

oops! I had a typo in the exeuction time for the sql query with HAVING clause:

Here are the execution times:

The Execution time for the SQL query with derived tables:
0.1676s, 0.1607s, 0.1736s

The Execution time for the SQL query with HAVING clause:
0.1647s, 0.1640s, 0.1697s

--
Mitra