Mysql select union error

Question

I am trying to display all records from table1 even if the catid not existing in table2 (all employee in table2 should have all catid from table1 with 0 days if not exising in table2) with the following sql query but getting an error:

Error Code: 1054. Unknown column 'catid' in 'group statement'

SQL

select empid,days from table2 
union 
select catid from table1 
group by empid, catid;

table1

catid
1
2
3

table2
empid catid days (computed column count(*))
1000    1    1
1000    3    1

Expected result:

empid  catid  days
1000    1    1
1000    2    0 <---catid 2 and days 0 if catid is not existing in table2 for empid 1000
1000    3    1

Answer 1

You can make it this way:

Table1:

mysql> SELECT * FROM table1;
+-------+
| catid |
+-------+
|     1 |
|     2 |
|     3 |
+-------+
3 rows in set (0.00 sec)

Table2:

mysql> SELECT * FROM table2;
+-------+-------+
| empid | catid |
+-------+-------+
|  1000 |     1 |
|  1000 |     3 |
|  1001 |     1 |
|  1002 |     2 |
|  1002 |     3 |
+-------+-------+
5 rows in set (0.00 sec)

Query:

SELECT
    t2.empid,
    t1.catid
    ,COUNT(t3.empid) as days
FROM table1 AS t1
LEFT JOIN (SELECT 
                DISTINCT empid,t1.catid 
            FROM table2 AS t2 
            LEFT JOIN table1 AS t1 ON (t1.catid>0)
            ) AS t2 -- Getting all empids from table2 and catids from table1
ON (t1.catid=t2.catid)
LEFT JOIN table2 AS t3 ON (t2.empid=t3.empid AND t3.catid=t2.catid)
GROUP BY t2.empid,t2.catid
ORDER BY t2.empid,t2.catid;

Test:

mysql> SELECT
    -> t2.empid,
    ->     t1.catid
    -> ,COUNT(t3.empid) as days
    -> FROM table1 AS t1
    -> LEFT JOIN (SELECT DISTINCT empid,t1.catid FROM table2 AS t2 LEFT JOIN table1 AS t1 ON (t1.catid>0)) AS t2
    -> ON (t1.catid=t2.catid)
    -> LEFT JOIN table2 AS t3 ON (t2.empid=t3.empid AND t3.catid=t2.catid)
    -> GROUP BY t2.empid,t2.catid
    -> ORDER BY t2.empid,t2.catid;
+-------+-------+------+
| empid | catid | days |
+-------+-------+------+
|  1000 |     1 |    1 |
|  1000 |     2 |    0 |
|  1000 |     3 |    1 |
|  1001 |     1 |    1 |
|  1001 |     2 |    0 |
|  1001 |     3 |    0 |
|  1002 |     1 |    0 |
|  1002 |     2 |    1 |
|  1002 |     3 |    1 |
+-------+-------+------+