Operand should contain 1 column(s) in a query

Question

I have this complex query that gives me Operand should contain 1 column(s) error when executing. I know that this error means there are some un-needed brackets but I dont know which ones.

This is the query:

SELECT 
 * 
FROM 
 deals 
WHERE 
 country_id = 2 AND 
 (city_id = 4 OR city_id = 0) AND 
 section_id = 2 AND 
 id IN (
  SELECT 
   deal_id 
  FROM 
   deal_repeat 
  WHERE 
   start_date = '2013-06-14') AND 
   business_id IN (
    SELECT 
     *, ( 3959 * acos( cos( radians('51') ) * cos( radians( lat ) ) * cos( radians( lng ) - radians('-114') ) + sin( radians('51') ) * sin( radians( lat ) ) ) ) AS distance 
    FROM 
     address 
    HAVING 
     distance < '25'
   ) 
  ORDER BY id DESC

`AND business_id IN (SELECT *, 3959...` is causing the error because you're selecting more than one column in the subquery - you need to select just one column (the corresponding business ID from "address"). — Ed Gibbs, Jun 14 '13 at 18:17

echo_Me · Answer 1 · 2013-06-14T18:46:43.083

2

instead of this

  AND business_id IN (SELECT *, ( 3959 * acos(

do this

 AND business_id IN (SELECT  ( 3959 * acos(

as Jonathan said : SELECT * returns all the columns and you should select one column to select business_id

edited Jun 14 '13 at 18:46

answered Jun 14 '13 at 18:18

echo_Me

37,078
5
58
78

1

+1. This is what I saw as the problem. And for an explanation for OP, subqueries generally should only return one column. Saying `SELECT *` returns all the columns. – Jonathan Kuhn Jun 14 '13 at 18:26
@JonathanKuhn edited and added your suggest :) thanks – echo_Me Jun 14 '13 at 18:47

score 0 · Answer 2 · answered Jun 14 '13 at 18:27

Without knowing your table structures, this is a little difficult to answer. The problem is that you're filtering by business_id being in a list of multiple columns (select *). The subquery should only contain a single column (a list of business ids's presumably). Instead, assuming you have a business id field in your address table, then this might be what you're looking for.

SELECT * 
FROM deals 
WHERE country_id = 2 
    AND city_id IN (4,0) 
    AND section_id = 2 
    AND id IN (
        SELECT deal_id 
        FROM deal_repeat 
        WHERE start_date = '2013-06-14') 
   AND business_id IN (
        SELECT business_id 
        FROM address 
        WHERE 3959*acos(cos(radians('51')) * 
            cos(radians(lat)) * 
            cos(radians(lng)-radians('-114'))+sin(radians('51')) * 
            sin(radians(lat)))  < '25') 
ORDER BY id DESC

thanks that was the solution – Tarek Abd El Monsif Jun 16 '13 at 17:33 — Tarek Abd El Monsif, Jun 16 '13 at 17:33
@TarekAbdElMonsif -- np, glad I could help! – sgeddes Jun 16 '13 at 17:39 — sgeddes, Jun 16 '13 at 17:39

Operand should contain 1 column(s) in a query

2 Answers2