Mysql Select Correlation 2 Levels Deep Subqueries
I'm stuck and this is driving me crazy... I'd like to use the code below to get x number of wins in the row. The problem is, mysql only allows 1 correlation level (not sure if im explaining this right) but @id is supposed to return the players id from the first select query. Any idea on how I can make this work?
select (@rank := if(@points = points, @rank +1, if(@points := points, @rank + 1, @rank + 1 ))) as rank, er.* from (select
@id := cc6_MensLeague_players.id as `id`,
@rounds := (
ifnull((select sum(p1.w)
from (
select
@r1 := (case when p1_r1 like '%' then 1 else 0 end) as r1,
@r2 := (case when p1_r2 like '%' then 1 else 0 end) as r2,
@r3 := (case when p1_r3 like '%' then 1 else 0 end) as r3,
@r4 := (case when p1_r4 like '%' then 1 else 0 end) as r4,
(@r1+@r2+@r3+@r4) as w
from cc6_MensLeague_scoresheets where p1 = @id
)p1),0)
) as `rounds`,
sum((@rounds*2)+(@rounds*1)) as `points`
from cc6_MensLeague_players group by `id`) er cross join (select @rank := 0, @points := -1) params order by id desc limit 9;
Here is some sample data to play with:
(Using dynamic @id alias will not work)
https://www.db-fiddle.com/f/ao2zgyiy8U5doGER6XZT23/3
(Using static id 8 works perfectly)
https://www.db-fiddle.com/f/2f2KvZt5MHVUuaP3WLPmAi/2
Here is what the resulting output should be:
rank id rounds points
1 10 4 12
2 9 4 12
3 8 8 24
4 7 8 24
5 6 6 18
6 5 4 12
7 4 0 0
8 3 0 0
9 2 0 0
Thanks for the help
mysql
asked Nov 26 '18 at 2:05
Yarnell ArsenaultYarnell Arsenault
33
add a comment |
I'm stuck and this is driving me crazy... I'd like to use the code below to get x number of wins in the row. The problem is, mysql only allows 1 correlation level (not sure if im explaining this right) but @id is supposed to return the players id from the first select query. Any idea on how I can make this work?
select (@rank := if(@points = points, @rank +1, if(@points := points, @rank + 1, @rank + 1 ))) as rank, er.* from (select
@id := cc6_MensLeague_players.id as `id`,
@rounds := (
ifnull((select sum(p1.w)
from (
select
@r1 := (case when p1_r1 like '%' then 1 else 0 end) as r1,
@r2 := (case when p1_r2 like '%' then 1 else 0 end) as r2,
@r3 := (case when p1_r3 like '%' then 1 else 0 end) as r3,
@r4 := (case when p1_r4 like '%' then 1 else 0 end) as r4,
(@r1+@r2+@r3+@r4) as w
from cc6_MensLeague_scoresheets where p1 = @id
)p1),0)
) as `rounds`,
sum((@rounds*2)+(@rounds*1)) as `points`
from cc6_MensLeague_players group by `id`) er cross join (select @rank := 0, @points := -1) params order by id desc limit 9;
Here is some sample data to play with:
(Using dynamic @id alias will not work)
https://www.db-fiddle.com/f/ao2zgyiy8U5doGER6XZT23/3
(Using static id 8 works perfectly)
https://www.db-fiddle.com/f/2f2KvZt5MHVUuaP3WLPmAi/2
Here is what the resulting output should be:
rank id rounds points
1 10 4 12
2 9 4 12
3 8 8 24
4 7 8 24
5 6 6 18
6 5 4 12
7 4 0 0
8 3 0 0
9 2 0 0
Thanks for the help
mysql
asked Nov 26 '18 at 2:05
Yarnell ArsenaultYarnell Arsenault
33
Minimal Sample data and corresponding expected output would help.
– Madhur Bhaiya
Nov 26 '18 at 4:54
Hi Madhur, thanks for the quick response. I have added some sample data for you to take a look at.
– Yarnell Arsenault
Nov 27 '18 at 14:44
And, what should be the expected output ? Please add that to the question in tabular format.
– Madhur Bhaiya
Nov 27 '18 at 14:47
Just added the expected output.
– Yarnell Arsenault
Nov 27 '18 at 17:01
add a comment |
I'm stuck and this is driving me crazy... I'd like to use the code below to get x number of wins in the row. The problem is, mysql only allows 1 correlation level (not sure if im explaining this right) but @id is supposed to return the players id from the first select query. Any idea on how I can make this work?
select (@rank := if(@points = points, @rank +1, if(@points := points, @rank + 1, @rank + 1 ))) as rank, er.* from (select
@id := cc6_MensLeague_players.id as `id`,
@rounds := (
ifnull((select sum(p1.w)
from (
select
@r1 := (case when p1_r1 like '%' then 1 else 0 end) as r1,
@r2 := (case when p1_r2 like '%' then 1 else 0 end) as r2,
@r3 := (case when p1_r3 like '%' then 1 else 0 end) as r3,
@r4 := (case when p1_r4 like '%' then 1 else 0 end) as r4,
(@r1+@r2+@r3+@r4) as w
from cc6_MensLeague_scoresheets where p1 = @id
)p1),0)
) as `rounds`,
sum((@rounds*2)+(@rounds*1)) as `points`
from cc6_MensLeague_players group by `id`) er cross join (select @rank := 0, @points := -1) params order by id desc limit 9;
Here is some sample data to play with:
(Using dynamic @id alias will not work)
https://www.db-fiddle.com/f/ao2zgyiy8U5doGER6XZT23/3
(Using static id 8 works perfectly)
https://www.db-fiddle.com/f/2f2KvZt5MHVUuaP3WLPmAi/2
Here is what the resulting output should be:
rank id rounds points
1 10 4 12
2 9 4 12
3 8 8 24
4 7 8 24
5 6 6 18
6 5 4 12
7 4 0 0
8 3 0 0
9 2 0 0
Thanks for the help
mysql
asked Nov 26 '18 at 2:05
Yarnell ArsenaultYarnell Arsenault
33
I'm stuck and this is driving me crazy... I'd like to use the code below to get x number of wins in the row. The problem is, mysql only allows 1 correlation level (not sure if im explaining this right) but @id is supposed to return the players id from the first select query. Any idea on how I can make this work?
select (@rank := if(@points = points, @rank +1, if(@points := points, @rank + 1, @rank + 1 ))) as rank, er.* from (select
@id := cc6_MensLeague_players.id as `id`,
@rounds := (
ifnull((select sum(p1.w)
from (
select
@r1 := (case when p1_r1 like '%' then 1 else 0 end) as r1,
@r2 := (case when p1_r2 like '%' then 1 else 0 end) as r2,
@r3 := (case when p1_r3 like '%' then 1 else 0 end) as r3,
@r4 := (case when p1_r4 like '%' then 1 else 0 end) as r4,
(@r1+@r2+@r3+@r4) as w
from cc6_MensLeague_scoresheets where p1 = @id
)p1),0)
) as `rounds`,
sum((@rounds*2)+(@rounds*1)) as `points`
from cc6_MensLeague_players group by `id`) er cross join (select @rank := 0, @points := -1) params order by id desc limit 9;
Here is some sample data to play with:
(Using dynamic @id alias will not work)
https://www.db-fiddle.com/f/ao2zgyiy8U5doGER6XZT23/3
(Using static id 8 works perfectly)
https://www.db-fiddle.com/f/2f2KvZt5MHVUuaP3WLPmAi/2
Here is what the resulting output should be:
rank id rounds points
1 10 4 12
2 9 4 12
3 8 8 24
4 7 8 24
5 6 6 18
6 5 4 12
7 4 0 0
8 3 0 0
9 2 0 0
Thanks for the help
mysql
mysql
asked Nov 26 '18 at 2:05
Yarnell ArsenaultYarnell Arsenault
33
asked Nov 26 '18 at 2:05
Yarnell ArsenaultYarnell Arsenault
33
edited Nov 27 '18 at 21:55
Yarnell Arsenault
asked Nov 26 '18 at 2:05
Yarnell ArsenaultYarnell Arsenault
33
asked Nov 26 '18 at 2:05
Yarnell ArsenaultYarnell Arsenault
33
asked Nov 26 '18 at 2:05
Yarnell ArsenaultYarnell Arsenault
33
33
Minimal Sample data and corresponding expected output would help.
– Madhur Bhaiya
Nov 26 '18 at 4:54
Hi Madhur, thanks for the quick response. I have added some sample data for you to take a look at.
– Yarnell Arsenault
Nov 27 '18 at 14:44
And, what should be the expected output ? Please add that to the question in tabular format.
– Madhur Bhaiya
Nov 27 '18 at 14:47
Just added the expected output.
– Yarnell Arsenault
Nov 27 '18 at 17:01
add a comment |
Minimal Sample data and corresponding expected output would help.
– Madhur Bhaiya
Nov 26 '18 at 4:54
Hi Madhur, thanks for the quick response. I have added some sample data for you to take a look at.
– Yarnell Arsenault
Nov 27 '18 at 14:44
And, what should be the expected output ? Please add that to the question in tabular format.
– Madhur Bhaiya
Nov 27 '18 at 14:47
Just added the expected output.
– Yarnell Arsenault
Nov 27 '18 at 17:01
Minimal Sample data and corresponding expected output would help.
– Madhur Bhaiya
Nov 26 '18 at 4:54
Minimal Sample data and corresponding expected output would help.
– Madhur Bhaiya
Nov 26 '18 at 4:54
Hi Madhur, thanks for the quick response. I have added some sample data for you to take a look at.
– Yarnell Arsenault
Nov 27 '18 at 14:44
Hi Madhur, thanks for the quick response. I have added some sample data for you to take a look at.
– Yarnell Arsenault
Nov 27 '18 at 14:44
And, what should be the expected output ? Please add that to the question in tabular format.
– Madhur Bhaiya
Nov 27 '18 at 14:47
And, what should be the expected output ? Please add that to the question in tabular format.
– Madhur Bhaiya
Nov 27 '18 at 14:47
Just added the expected output.
– Yarnell Arsenault
Nov 27 '18 at 17:01
Just added the expected output.
– Yarnell Arsenault
Nov 27 '18 at 17:01
add a comment |
0
active
oldest
votes
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%2f53473913%2fmysql-select-correlation-2-levels-deep-subqueries%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
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%2f53473913%2fmysql-select-correlation-2-levels-deep-subqueries%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
Minimal Sample data and corresponding expected output would help.
– Madhur Bhaiya
Nov 26 '18 at 4:54
Hi Madhur, thanks for the quick response. I have added some sample data for you to take a look at.
– Yarnell Arsenault
Nov 27 '18 at 14:44
And, what should be the expected output ? Please add that to the question in tabular format.
– Madhur Bhaiya
Nov 27 '18 at 14:47
Just added the expected output.
– Yarnell Arsenault
Nov 27 '18 at 17:01