CODEWITHSUNDEEP
mysql
fat_flying_pigs
fat_flying_pigs

Reputation: 90

Empty table causing in NULL in all SELECT fields

I have a table, chat, that controls what messages have been read from all the other tables. For new users, I am inserting new values.

INSERT INTO chat VALUES (1,aaa,bbb,ccc)
SELECT
MAX(chatA.postOrder) AS aaa,
MAX(chatB.postOrder) AS bbb,
MAX(chatC.postOrder) AS ccc
FROM `chatA`, `chatB`, `chatC`
WHERE 1

For ChatB and ChatC, postOrder exists and they both have values. Everything works correctly. Problem is, sometimes a chat is empty (in this case, chatA). When this happens, bbb and ccc become NULL. If I remove this line MAX(chatA.postOrder) AS aaa,, the values are still NULL.

I'm stuck here, unable to figure out a way to fix this problem. Changes made in the select field such as IFNULL(chatA.postOrder,0) AS aaa, have no effect on the results. It appears that chatA in the FROM field causes the results of bbb and ccc to be NULL.

Edit: Formatting

Upvotes: 0

Views: 85

Answers (2)

spencer7593
spencer7593

Reputation: 108410

To ensure that return a row when any of those tables is empty, you could something like this:

SELECT MAX(IF(t.chat='a',t.postOrder,NULL) AS aaa
     , MAX(IF(t.chat='b',t.postOrder,NULL) AS bbb
     , MAX(IF(t.chat='c',t.postOrder,NULL) AS ccc
  FROM ( SELECT 'a' AS `chat`, MAX(ta.postOrder) AS postOrder FROM `chatA` ta
          UNION ALL
         SELECT 'b', MAX(tb.postOrder) FROM `chatB` tb
          UNION ALL
         SELECT 'c', MAX(tc.postOrder) FROM `chatB` tc
       ) t

NOTES: The inline view (assigned an alias of t above) returns the maximum postOrder from each of the three tables. If the table is empty (has no rows), THE SELECT from that table won't return a row. But since the rows will be concatenated together with UNION ALL operators. That gives a single result set, which the outer query can select from. On each of rows, we add a constant literal discriminator ('a', 'b' or 'c') that identifies which table the row was returned from.

The "trick" in the outer query is to test the discriminator column, and return the value for postOrder only if that column matches the one specified in the IF. Otherwise, we return a NULL. Wrapping that IF() expression in a MAX() aggregate function effectively collapses the three rows into a single row.

Your query is effectively:

SELECT MAX(a.postOrder) AS aaa
     , MAX(a.postOrder) AS bbb
     , MAX(a.postOrder) AS ccc
  FROM `chatA` a
 CROSS
  JOIN `chatB` b
 CROSS
  JOIN `chatC` c

Because of the JOIN operations, if any of those three tables is empty (does not return a row) then the query does not return any row at all.

Upvotes: 0

skv
skv

Reputation: 1803

Why dont you try union all 

SELECT MAX(aaa) AS aaa, MAX(bbb) AS bbb, MAX(ccc) AS ccc from (
SELECT MAX(IFNULL(chatA.postOrder,0)) AS aaa, 0 as bbb, 0 AS ccc FROM `chatA`
UNION ALL
SELECT 0 AS aaa, MAX(IFNULL(chatB.postOrder,0)) AS bbb, 0 AS ccc FROM `chatB`
UNION ALL
SELECT 0 AS aaa, 0 AS bbb, MAX(IFNULL(chatC.postOrder,0)) AS ccc FROM `chatC`) 
AS derived_table;

This is more appropriate because you are not trying to "Join" the tables. I am assuming the values wont be negative, if they would be then 0 would not be a good number probably a negative number that will never appear in your results would do

Upvotes: 1

Related Questions