CODEWITHSUNDEEP
mysqlsqlsubquery
albator
albator

Reputation: 859

Operand should have 1 column in mysql

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?

Upvotes: 0

Views: 58

Answers (3)

gen_Eric
gen_Eric

Reputation: 227240

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

Upvotes: 3

Jorge Campos
Jorge Campos

Reputation: 23361

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)

Upvotes: 0

Santhosh
Santhosh

Reputation: 1791

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)

Upvotes: 0

Related Questions