Subquery returns more than 1 row error although I'm using “IN” keyword
Although I'm using "IN" keyword I keep getting Subquery returns more than 1 row error.
Here is my query:
SELECT *
FROM `message`
WHERE `id_receiver` IN (
IF( `to_`='user' , '36' ,
IF( `to_`='group' , (
SELECT `id`
FROM (
SELECT *
FROM `user_position`
ORDER BY `id`) `user_position`,
(SELECT @pv := '100') INITIALISATION
WHERE FIND_IN_SET( `parent_id` , @pv ) > 0
AND @pv := concat( @pv , ',' , `id` )
), (
SELECT `message_group`.`id`
FROM `message_group`
LEFT JOIN `message_group_member`
ON `message_group_member`.`id_group`=`message_group`.`id`
WHERE `message_group_member`.`id_user`='36'
)
)
)
AND `seen`='0'
AND `receiver_delete`='0'
AND `till_date` IS NULL
mysql mysqli
edited Jan 19 at 11:16
trincot
122k1586119
asked Jan 19 at 11:08
Alireza FeyzyabAlireza Feyzyab
54
add a comment |
Although I'm using "IN" keyword I keep getting Subquery returns more than 1 row error.
Here is my query:
SELECT *
FROM `message`
WHERE `id_receiver` IN (
IF( `to_`='user' , '36' ,
IF( `to_`='group' , (
SELECT `id`
FROM (
SELECT *
FROM `user_position`
ORDER BY `id`) `user_position`,
(SELECT @pv := '100') INITIALISATION
WHERE FIND_IN_SET( `parent_id` , @pv ) > 0
AND @pv := concat( @pv , ',' , `id` )
), (
SELECT `message_group`.`id`
FROM `message_group`
LEFT JOIN `message_group_member`
ON `message_group_member`.`id_group`=`message_group`.`id`
WHERE `message_group_member`.`id_user`='36'
)
)
)
AND `seen`='0'
AND `receiver_delete`='0'
AND `till_date` IS NULL
mysql mysqli
edited Jan 19 at 11:16
trincot
122k1586119
asked Jan 19 at 11:08
Alireza FeyzyabAlireza Feyzyab
54
1
Could you please put a better formatting for your SQL code??
– Adib Aroui
Jan 19 at 11:11
add a comment |
Although I'm using "IN" keyword I keep getting Subquery returns more than 1 row error.
Here is my query:
SELECT *
FROM `message`
WHERE `id_receiver` IN (
IF( `to_`='user' , '36' ,
IF( `to_`='group' , (
SELECT `id`
FROM (
SELECT *
FROM `user_position`
ORDER BY `id`) `user_position`,
(SELECT @pv := '100') INITIALISATION
WHERE FIND_IN_SET( `parent_id` , @pv ) > 0
AND @pv := concat( @pv , ',' , `id` )
), (
SELECT `message_group`.`id`
FROM `message_group`
LEFT JOIN `message_group_member`
ON `message_group_member`.`id_group`=`message_group`.`id`
WHERE `message_group_member`.`id_user`='36'
)
)
)
AND `seen`='0'
AND `receiver_delete`='0'
AND `till_date` IS NULL
mysql mysqli
edited Jan 19 at 11:16
trincot
122k1586119
asked Jan 19 at 11:08
Alireza FeyzyabAlireza Feyzyab
54
Although I'm using "IN" keyword I keep getting Subquery returns more than 1 row error.
Here is my query:
SELECT *
FROM `message`
WHERE `id_receiver` IN (
IF( `to_`='user' , '36' ,
IF( `to_`='group' , (
SELECT `id`
FROM (
SELECT *
FROM `user_position`
ORDER BY `id`) `user_position`,
(SELECT @pv := '100') INITIALISATION
WHERE FIND_IN_SET( `parent_id` , @pv ) > 0
AND @pv := concat( @pv , ',' , `id` )
), (
SELECT `message_group`.`id`
FROM `message_group`
LEFT JOIN `message_group_member`
ON `message_group_member`.`id_group`=`message_group`.`id`
WHERE `message_group_member`.`id_user`='36'
)
)
)
AND `seen`='0'
AND `receiver_delete`='0'
AND `till_date` IS NULL
mysql mysqli
mysql mysqli
edited Jan 19 at 11:16
trincot
122k1586119
asked Jan 19 at 11:08
Alireza FeyzyabAlireza Feyzyab
54
edited Jan 19 at 11:16
trincot
122k1586119
asked Jan 19 at 11:08
Alireza FeyzyabAlireza Feyzyab
54
edited Jan 19 at 11:16
trincot
122k1586119
edited Jan 19 at 11:16
trincot
122k1586119
edited Jan 19 at 11:16
trincot
122k1586119
122k1586119
asked Jan 19 at 11:08
Alireza FeyzyabAlireza Feyzyab
54
asked Jan 19 at 11:08
Alireza FeyzyabAlireza Feyzyab
54
asked Jan 19 at 11:08
Alireza FeyzyabAlireza Feyzyab
54
54
1
Could you please put a better formatting for your SQL code??
– Adib Aroui
Jan 19 at 11:11
add a comment |
1
Could you please put a better formatting for your SQL code??
– Adib Aroui
Jan 19 at 11:11
1
1
Could you please put a better formatting for your SQL code??
– Adib Aroui
Jan 19 at 11:11
Could you please put a better formatting for your SQL code??
– Adib Aroui
Jan 19 at 11:11
add a comment |
1 Answer
1
active
oldest
votes
The inner select queries return multiple rows. That is not allowed within an if expression. It would be allowed in a pure in expression, but as soon as you use if, that no longer holds.
So translate your idea into a series of OR:
SELECT *
FROM message
WHERE seen = '0'
AND receiver_delete ='0'
AND till_date IS NULL
AND ( (to_ = 'user' AND id_receiver = '36')
OR (to_ = 'group' AND id_receiver IN (
SELECT id
FROM (
SELECT *
FROM user_position
ORDER BY id) user_position,
(SELECT @pv := '100') initialisation
WHERE FIND_IN_SET(parent_id, @pv) > 0
AND @pv := concat(@pv , ',' , id)
))
OR (to_ NOT IN ('user', 'group') AND id_receiver IN (
SELECT message_group.id
FROM message_group
LEFT JOIN message_group_member
ON message_group_member.id_group=message_group.id
WHERE message_group_member.id_user='36'
))
)
I note that you use variable assignments in the first inner query. Be cautious with this, as there is no guarantee about the order of execution. I see that sub query is inspired by another answer of mine, but please take note of all the disclaimers and warnings in that answer. They apply here.
If you can, move to MySql 8 and the recursive with syntax.
answered Jan 19 at 11:31
trincottrincot
122k1586119
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54266439%2fsubquery-returns-more-than-1-row-error-although-im-using-in-keyword%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
The inner select queries return multiple rows. That is not allowed within an if expression. It would be allowed in a pure in expression, but as soon as you use if, that no longer holds.
So translate your idea into a series of OR:
SELECT *
FROM message
WHERE seen = '0'
AND receiver_delete ='0'
AND till_date IS NULL
AND ( (to_ = 'user' AND id_receiver = '36')
OR (to_ = 'group' AND id_receiver IN (
SELECT id
FROM (
SELECT *
FROM user_position
ORDER BY id) user_position,
(SELECT @pv := '100') initialisation
WHERE FIND_IN_SET(parent_id, @pv) > 0
AND @pv := concat(@pv , ',' , id)
))
OR (to_ NOT IN ('user', 'group') AND id_receiver IN (
SELECT message_group.id
FROM message_group
LEFT JOIN message_group_member
ON message_group_member.id_group=message_group.id
WHERE message_group_member.id_user='36'
))
)
I note that you use variable assignments in the first inner query. Be cautious with this, as there is no guarantee about the order of execution. I see that sub query is inspired by another answer of mine, but please take note of all the disclaimers and warnings in that answer. They apply here.
If you can, move to MySql 8 and the recursive with syntax.
answered Jan 19 at 11:31
trincottrincot
122k1586119
add a comment |
The inner select queries return multiple rows. That is not allowed within an if expression. It would be allowed in a pure in expression, but as soon as you use if, that no longer holds.
So translate your idea into a series of OR:
SELECT *
FROM message
WHERE seen = '0'
AND receiver_delete ='0'
AND till_date IS NULL
AND ( (to_ = 'user' AND id_receiver = '36')
OR (to_ = 'group' AND id_receiver IN (
SELECT id
FROM (
SELECT *
FROM user_position
ORDER BY id) user_position,
(SELECT @pv := '100') initialisation
WHERE FIND_IN_SET(parent_id, @pv) > 0
AND @pv := concat(@pv , ',' , id)
))
OR (to_ NOT IN ('user', 'group') AND id_receiver IN (
SELECT message_group.id
FROM message_group
LEFT JOIN message_group_member
ON message_group_member.id_group=message_group.id
WHERE message_group_member.id_user='36'
))
)
I note that you use variable assignments in the first inner query. Be cautious with this, as there is no guarantee about the order of execution. I see that sub query is inspired by another answer of mine, but please take note of all the disclaimers and warnings in that answer. They apply here.
If you can, move to MySql 8 and the recursive with syntax.
answered Jan 19 at 11:31
trincottrincot
122k1586119
add a comment |
The inner select queries return multiple rows. That is not allowed within an if expression. It would be allowed in a pure in expression, but as soon as you use if, that no longer holds.
So translate your idea into a series of OR:
SELECT *
FROM message
WHERE seen = '0'
AND receiver_delete ='0'
AND till_date IS NULL
AND ( (to_ = 'user' AND id_receiver = '36')
OR (to_ = 'group' AND id_receiver IN (
SELECT id
FROM (
SELECT *
FROM user_position
ORDER BY id) user_position,
(SELECT @pv := '100') initialisation
WHERE FIND_IN_SET(parent_id, @pv) > 0
AND @pv := concat(@pv , ',' , id)
))
OR (to_ NOT IN ('user', 'group') AND id_receiver IN (
SELECT message_group.id
FROM message_group
LEFT JOIN message_group_member
ON message_group_member.id_group=message_group.id
WHERE message_group_member.id_user='36'
))
)
I note that you use variable assignments in the first inner query. Be cautious with this, as there is no guarantee about the order of execution. I see that sub query is inspired by another answer of mine, but please take note of all the disclaimers and warnings in that answer. They apply here.
If you can, move to MySql 8 and the recursive with syntax.
answered Jan 19 at 11:31
trincottrincot
122k1586119
The inner select queries return multiple rows. That is not allowed within an if expression. It would be allowed in a pure in expression, but as soon as you use if, that no longer holds.
So translate your idea into a series of OR:
SELECT *
FROM message
WHERE seen = '0'
AND receiver_delete ='0'
AND till_date IS NULL
AND ( (to_ = 'user' AND id_receiver = '36')
OR (to_ = 'group' AND id_receiver IN (
SELECT id
FROM (
SELECT *
FROM user_position
ORDER BY id) user_position,
(SELECT @pv := '100') initialisation
WHERE FIND_IN_SET(parent_id, @pv) > 0
AND @pv := concat(@pv , ',' , id)
))
OR (to_ NOT IN ('user', 'group') AND id_receiver IN (
SELECT message_group.id
FROM message_group
LEFT JOIN message_group_member
ON message_group_member.id_group=message_group.id
WHERE message_group_member.id_user='36'
))
)
I note that you use variable assignments in the first inner query. Be cautious with this, as there is no guarantee about the order of execution. I see that sub query is inspired by another answer of mine, but please take note of all the disclaimers and warnings in that answer. They apply here.
If you can, move to MySql 8 and the recursive with syntax.
answered Jan 19 at 11:31
trincottrincot
122k1586119
answered Jan 19 at 11:31
trincottrincot
122k1586119
answered Jan 19 at 11:31
trincottrincot
122k1586119
answered Jan 19 at 11:31
trincottrincot
122k1586119
122k1586119
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54266439%2fsubquery-returns-more-than-1-row-error-although-im-using-in-keyword%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
Could you please put a better formatting for your SQL code??
– Adib Aroui
Jan 19 at 11:11