Operand should have 1 column in mysql

Question

I'm getting the error "operand should be contain 1 column".

Yes, I try to compare 1 column with 3, so is what I want to achieve possible?

Clear exemple: I have a table test

 id   profile_id  result_number
 10     1232        3
 10     3263        5
 10     2222        4
 10     2321        1

Actually, I have 3 query and I want to get all in one query. First, I get the result number of profile_id 2222 (return 4)

 SELECT `result_number` FROM test WHERE id=10 AND `profile_id`=2222

Next, I get the profile_id who have result_number - 1

SELECT `profile_id` FROM test
 WHERE id=10 AND `result_number` = 4 - 1

(return 3)

Finally, I get the profile_id who have result_number + 1

SELECT `profile_id` FROM test WHERE id=10 AND `result_number` = 4 + 1

(return 5)

result expected :

profile_id
    3263
    2222
    1232

Is it possible to achieve it?

What's the query that gives you the error? – gen_Eric Nov 13 '13 at 20:15 — gen_Eric, Nov 13 '13 at 20:15

gen_Eric · Accepted Answer · 2013-11-13T20:26:10.497

3

You can JOIN a subquery if it contains multiple rows:

SELECT profile_id
FROM test t, (
  SELECT result_number
  FROM test
  WHERE id = 10
  AND profile_id = 2222
) q
WHERE t.result_number BETWEEN q.result_number-1 AND q.result_number+1
AND id = 10
ORDER BY t.result_number DESC

DEMO: http://sqlfiddle.com/#!2/6b922/1

edited Nov 13 '13 at 20:26

answered Nov 13 '13 at 20:19

gen_Eric

223,194
41
299
337

score 0 · Answer 2 · answered Nov 13 '13 at 20:16

0

with a  as
(
SELECT `result_number`, profile_id FROM test WHERE id=10 AND `profile_id`=2222
)

SELECT PROFILE_ID FROM A
UNION
SELECT B.PROFILE_ID FROM TEST AS B, A WHERE B.RESULT_NUMBER = (A.RESULT_NUMBER - 1)
UNION
SELECT C.PROFILE_ID FROM TEST AS C, A WHERE C.RESULT_NUMBER = (A.RESULT_NUMBER + 1)

answered Nov 13 '13 at 20:16

Santhosh

1,771
1
15
25

Yeah, I am wrong to overlook that MYSQL tag. MYSQL does not have WITH Clause. Your answer above that uses BETWEEN is right. – Santhosh Nov 13 '13 at 20:22

score 0 · Answer 3 · answered Nov 13 '13 at 20:19

0

I think this would do what you want:

select *
from test 
where id=10 and ( result_number= 4 or result_number=4-1 or result_number = 4+1)

answered Nov 13 '13 at 20:19

Jorge Campos

22,647
7
56
87

The problem is the `4` is from another query. He wants to combine that query into this one. – gen_Eric Nov 13 '13 at 20:20
exacly, at first stage i don't know result number = 4 . I'm pretty sure there's a solution somewhere..:) – albator Nov 13 '13 at 20:20
Oooh.. so the @RocketHazmat answer will fit your problem. – Jorge Campos Nov 13 '13 at 20:21

Operand should have 1 column in mysql

3 Answers3