Mysql union select 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'

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    8
1000    3   10

expected result:

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

score 0 · Answer 1 · answered Oct 27 '15 at 05:38

0

That is not the function of the union statement. Union statement does a set like capability which merging two sets. What you are looking for a is a join with the table 1 where you do a count and group by catid. Your data model to achieve this output itself is grievously wrong ;)

select employeeid, catid, sum(days) from table1, table2 group by employeeid, catid;

answered Oct 27 '15 at 05:38

Bijur Vallark

1
1

this retrieves all catid from table1 and also the empid but days are all same value. Actually days are count(*) not an actual column in table2 – Hafsa Abdul gafoor Oct 27 '15 at 06:27

Sashi Kant · Answer 2 · 2015-10-27T05:56:11.497

0

You just need a LEFT JOIN:

    Select tab2.empid, tab2.catid, ifnull(tab2.days, 0)
    from tab2 
    left join tab1 on tab2.catid = tab1.catid

Please note : While doing a UNION the number and type of the columns present in the first select should be the same as the next Selects.

So you need to first make the select columns in sync first.

edited Oct 27 '15 at 05:56

answered Oct 27 '15 at 05:44

Sashi Kant

13,277
9
44
71

score 0 · Answer 3 · edited Oct 27 '15 at 07:29

0

can you check this and add empid similarly.

SELECT TABLE1.CATID, IFNULL(TABLE2.DAYS,0) FROM table1 LEFT OUTER JOIN
table2 ON table1.catid = table2.catid

edited Oct 27 '15 at 07:29

Shaminder Singh

1,283
2
18
31

answered Oct 27 '15 at 06:10

Don

102
1
10

FYI http://stackoverflow.com/questions/15425740/are-left-outer-joins-and-left-joins-the-same – Sashi Kant Oct 27 '15 at 09:26

Samrat Aher · Answer 4 · 2015-10-27T12:55:05.053

0

Please use LEFT JOIN with IFNULL.

Select table2.empid, table1.catid, IFNULL(table2.days, 0) from table2 
LEFT JOIN table1 ON table2.catid = table1.catid;

edited Oct 27 '15 at 12:55

answered Oct 27 '15 at 06:19

Samrat Aher

76
5

NVL is not there in MYSQL – Sashi Kant Oct 27 '15 at 06:20
You are right Sashi Kant. Should have used IFNULL. – Samrat Aher Oct 27 '15 at 06:45
Please edit once again, since it is not allowing me to undo the down vote. – Sashi Kant Oct 27 '15 at 08:59

Mysql union select error

4 Answers4