SQL error 1222 different number of coloumns

Question

Hey i keep getting the error 1222 "The used select statements have a different number of coloumns. I have found out the error is in this section

JOIN
 ( SELECT 3 snpos,x.name fa1,x.mass mz_fa1,x.catoms catomsfa1,x.dbnum dbnumfa1,y.name fa2,y.mass mz_fa2,y.catoms catomsfa2,y.dbnum dbnumfa2,z.name fa3,z.mass mz_fa3,z.catoms catomsfa3, z.dbnum dbnumfa3, x.mass+y.mass+z.mass total, x.catoms+y.catoms+z.catoms totcatoms, x.dbnum+y.dbnum+z.dbnum totdbnum FROM ms2frag x JOIN ms2frag y ON y.id <= x.id JOIN  ms2frag z ON z.id <= y.id
   UNION ALL
   SELECT 2      ,x.name fa1,x.mass mz_fa1,x.catoms catomsfa1,x.dbnum dbnumfa1,y.name fa2,y.mass mz_fa2,y.catoms catomsfa2,y.dbnum dbnumfa2, x.mass+y.mass total, x.catoms+y.catoms totcatoms, x.dbnum+y.dbnum totdbnum FROM ms2frag x JOIN ms2frag y ON y.id <= x.id
   UNION ALL
   SELECT 1,x.name fa1,x.mass mz_fa1, x.catoms catomsfa1, NULL,0,NULL,0, mass, catoms FROM ms2frag
 ) ms2frag

So just to explain in has to pick which of the following lines it should be according to if snpos =3 or 2 or 1.

The other parts of the code works, only this one which doesn't.

Well, the error is very clear, you are using `UNION` on 3 queries that have a different number of columns — Lamak, May 13 '14 at 14:11

score 6 · Answer 1 · answered May 13 '14 at 14:10

6

When using UNION, you have to make sure each of your statements returns the same number of columns. In your example, each query returns a different number of columns and won't work.

answered May 13 '14 at 14:10

sgeddes

62,311
6
61
83

But should i really add in 4 NULL statements? (from 3 to 2) or are there a smarter way to do it? – Klaus Joe Christiansen May 13 '14 at 14:14
1

Yes, you must have equal number of columns and type (using implicit convertion) in each select statement in UNION. – Bogdan Bogdanov May 13 '14 at 14:16

score 0 · Answer 2 · answered May 13 '14 at 14:13

To use Union you must provide equal number of columns for all select statements inside. It is obvious that you have different number of column in fisrt and third select. Also the column types in the corresponding columns must be of implicitly convert to each other.

SQL error 1222 different number of coloumns

2 Answers2