CODEWITHSUNDEEP
mysqlsql
jason
jason

Reputation: 3962

SELECT statements have a different number of columns

I have tried using the code below and it says

The used SELECT statements have a different number of columns

because distance column in added is more in 2nd query .How do I resolve something like this or can I get the order right without distance column?I really appreciate any help.THanks in Advance.

SELECT * FROM
(
   SELECT * FROM user u
   INNER JOIN employee e ON (u.empid = e.empid)
   INNER JOIN awards a ON (u.empid = a.empid)
   WHERE u.empid = 123

   UNION

   SELECT * ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-122) )+ sin( radians(37) ) * sin( radians( lat ) ) ) ) AS distance
   FROM user u
   INNER JOIN employee e ON (u.empid = e.empid)
   INNER JOIN awards a ON (u.empid = a.empid)
   HAVING distance < 25
   ORDER BY distance
) a
ORDER BY timestamp DESC

Upvotes: 1

Views: 965

Answers (2)

Barb C. Goldstein
Barb C. Goldstein

Reputation: 454

It's because your 1st SELECT and 2nd SELECT have varying number of columns, since you introduced Distance in 2nd SELECT.

So try making Distance in 1st query null or 0 as follows.

SELECT * FROM
(
   SELECT *, 0 as Distance
   FROM user u
   INNER JOIN employee e ON (u.empid = e.empid)
   INNER JOIN awards a ON (u.empid = a.empid)
   WHERE u.empid = 123

   UNION

   SELECT *, ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-122) )+ sin( radians(37) ) * sin( radians( lat ) ) ) ) AS distance
   FROM user u
   INNER JOIN employee e ON (u.empid = e.empid)
   INNER JOIN awards a ON (u.empid = a.empid)
   HAVING distance < 25
   ORDER BY distance
) a
ORDER BY timestamp DESC

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270021

You would fix this by adding distance to the first query:

SELECT * FROM
(
   SELECT *, NULL as distance FROM user u
   INNER JOIN employee e ON (u.empid = e.empid)
   INNER JOIN awards a ON (u.empid = a.empid)
   WHERE u.empid = 123

   UNION

   SELECT *,  ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-122) )+ sin( radians(37) ) * sin( radians( lat ) ) ) ) AS distance
   FROM user u
   INNER JOIN employee e ON (u.empid = e.empid)
   INNER JOIN awards a ON (u.empid = a.empid)
   HAVING distance < 25
   ORDER BY distance
) a
ORDER BY timestamp DESC;

Upvotes: 0

Related Questions