Counting a left join depending on other left join
I have this query:
SELECT options.id, options.text,
COUNT(options2.id) AS num_children
FROM options
JOIN history_uids AS uids
ON uids.uid = options.id
LEFT JOIN options AS options2
ON (options.id = options2.id_parent)
LEFT JOIN history_uids AS uids1
ON uids1.uid = options2.id
WHERE (
options.id = 25
AND (uids1.active = 1 OR
options2.id IS NULL) # Problem
)
GROUP BY `options`.`id`
There is an options table, and another, history_uids, which has a column named uid with the id of every options, and an active column set to 1 or 0.
I am expecting to get a result with:
- The ID and the text of the option which has the
id25, and which hasactiveset to 1 inhistory_uids. - The number of options which have an
id_parentequal to theid(25) and for whichactiveinhistory_uidsis set to 1
So whatever is this number I should get my row if it has active set to 1. I cannot understand how to achieve this: I wanna set as last condition uids1.active = 1 OR "options2 doesn't exist", but to get this I should have my active condition in the ON of options2, which is not possible because at that moment the table history_uids is not yet referenced...
In my case the row has active set to 1 and has 5 children with active set to 0, so I should get my row with num_children set at 0. Instead whatever combination I do in the JOIN or the WHERE I get either num_children set to 5 or no result at all.
Thanks for your help (and for reading!)
Here is the full structure and data for testing:
CREATE TABLE `history_uids` (
`uid` int(10) NOT NULL,
`active` tinyint(1) NOT NULL DEFAULT '1'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `history_uids` (`uid`, `active`) VALUES
(1, 1),
(2, 0),
(3, 0),
(4, 0),
(5, 0),
(6, 0);
CREATE TABLE `options` (
`id` int(10) NOT NULL,
`id_parent` int(10) DEFAULT NULL,
`text` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `options` (`id`, `id_parent`, `text`) VALUES
(1, NULL, 'parent active'),
(2, 1, 'child 1 inactive'),
(3, 1, 'child 2 inactive'),
(4, 1, 'child 3 inactive'),
(5, 1, 'child 4 inactive'),
(6, 1, 'child 5 inactive');
ALTER TABLE `history_uids`
ADD PRIMARY KEY (`uid`);
ALTER TABLE `options`
ADD PRIMARY KEY (`id`),
ADD KEY `id_parent` (`id_parent`);
ALTER TABLE `options`
ADD CONSTRAINT `ibfk_3` FOREIGN KEY (`id_parent`) REFERENCES `options` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `ibfk_4` FOREIGN KEY (`id`) REFERENCES `history_uids` (`uid`) ON DELETE CASCADE ON UPDATE CASCADE;
mysql sql
asked Nov 21 '18 at 19:19
NababNabab
2,0581426
add a comment |
I have this query:
SELECT options.id, options.text,
COUNT(options2.id) AS num_children
FROM options
JOIN history_uids AS uids
ON uids.uid = options.id
LEFT JOIN options AS options2
ON (options.id = options2.id_parent)
LEFT JOIN history_uids AS uids1
ON uids1.uid = options2.id
WHERE (
options.id = 25
AND (uids1.active = 1 OR
options2.id IS NULL) # Problem
)
GROUP BY `options`.`id`
There is an options table, and another, history_uids, which has a column named uid with the id of every options, and an active column set to 1 or 0.
I am expecting to get a result with:
- The ID and the text of the option which has the
id25, and which hasactiveset to 1 inhistory_uids. - The number of options which have an
id_parentequal to theid(25) and for whichactiveinhistory_uidsis set to 1
So whatever is this number I should get my row if it has active set to 1. I cannot understand how to achieve this: I wanna set as last condition uids1.active = 1 OR "options2 doesn't exist", but to get this I should have my active condition in the ON of options2, which is not possible because at that moment the table history_uids is not yet referenced...
In my case the row has active set to 1 and has 5 children with active set to 0, so I should get my row with num_children set at 0. Instead whatever combination I do in the JOIN or the WHERE I get either num_children set to 5 or no result at all.
Thanks for your help (and for reading!)
Here is the full structure and data for testing:
CREATE TABLE `history_uids` (
`uid` int(10) NOT NULL,
`active` tinyint(1) NOT NULL DEFAULT '1'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `history_uids` (`uid`, `active`) VALUES
(1, 1),
(2, 0),
(3, 0),
(4, 0),
(5, 0),
(6, 0);
CREATE TABLE `options` (
`id` int(10) NOT NULL,
`id_parent` int(10) DEFAULT NULL,
`text` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `options` (`id`, `id_parent`, `text`) VALUES
(1, NULL, 'parent active'),
(2, 1, 'child 1 inactive'),
(3, 1, 'child 2 inactive'),
(4, 1, 'child 3 inactive'),
(5, 1, 'child 4 inactive'),
(6, 1, 'child 5 inactive');
ALTER TABLE `history_uids`
ADD PRIMARY KEY (`uid`);
ALTER TABLE `options`
ADD PRIMARY KEY (`id`),
ADD KEY `id_parent` (`id_parent`);
ALTER TABLE `options`
ADD CONSTRAINT `ibfk_3` FOREIGN KEY (`id_parent`) REFERENCES `options` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `ibfk_4` FOREIGN KEY (`id`) REFERENCES `history_uids` (`uid`) ON DELETE CASCADE ON UPDATE CASCADE;
mysql sql
asked Nov 21 '18 at 19:19
NababNabab
2,0581426
See meta.stackoverflow.com/questions/333952/…
– Strawberry
Nov 21 '18 at 19:24
1
COUNT(options2.id)only counts non-null values, so the rows whereoptions2.id IS NULLwon't be counted.
– Barmar
Nov 21 '18 at 19:48
Can you show some example input and the desired result? I suspect what you need is two queries that you combine withUNION, but it's hard to understand your description.
– Barmar
Nov 21 '18 at 19:50
I have added structure and data if you want to test the query. The goal is to get the row withnum_childrenset at0(as all child options have the correspondinghistory_uids.activeset to 0)
– Nabab
Nov 21 '18 at 19:59
@Barmar I would like this as a result:{id: 1, text: 'parent active', num_children: 0}
– Nabab
Nov 21 '18 at 22:06
add a comment |
I have this query:
SELECT options.id, options.text,
COUNT(options2.id) AS num_children
FROM options
JOIN history_uids AS uids
ON uids.uid = options.id
LEFT JOIN options AS options2
ON (options.id = options2.id_parent)
LEFT JOIN history_uids AS uids1
ON uids1.uid = options2.id
WHERE (
options.id = 25
AND (uids1.active = 1 OR
options2.id IS NULL) # Problem
)
GROUP BY `options`.`id`
There is an options table, and another, history_uids, which has a column named uid with the id of every options, and an active column set to 1 or 0.
I am expecting to get a result with:
- The ID and the text of the option which has the
id25, and which hasactiveset to 1 inhistory_uids. - The number of options which have an
id_parentequal to theid(25) and for whichactiveinhistory_uidsis set to 1
So whatever is this number I should get my row if it has active set to 1. I cannot understand how to achieve this: I wanna set as last condition uids1.active = 1 OR "options2 doesn't exist", but to get this I should have my active condition in the ON of options2, which is not possible because at that moment the table history_uids is not yet referenced...
In my case the row has active set to 1 and has 5 children with active set to 0, so I should get my row with num_children set at 0. Instead whatever combination I do in the JOIN or the WHERE I get either num_children set to 5 or no result at all.
Thanks for your help (and for reading!)
Here is the full structure and data for testing:
CREATE TABLE `history_uids` (
`uid` int(10) NOT NULL,
`active` tinyint(1) NOT NULL DEFAULT '1'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `history_uids` (`uid`, `active`) VALUES
(1, 1),
(2, 0),
(3, 0),
(4, 0),
(5, 0),
(6, 0);
CREATE TABLE `options` (
`id` int(10) NOT NULL,
`id_parent` int(10) DEFAULT NULL,
`text` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `options` (`id`, `id_parent`, `text`) VALUES
(1, NULL, 'parent active'),
(2, 1, 'child 1 inactive'),
(3, 1, 'child 2 inactive'),
(4, 1, 'child 3 inactive'),
(5, 1, 'child 4 inactive'),
(6, 1, 'child 5 inactive');
ALTER TABLE `history_uids`
ADD PRIMARY KEY (`uid`);
ALTER TABLE `options`
ADD PRIMARY KEY (`id`),
ADD KEY `id_parent` (`id_parent`);
ALTER TABLE `options`
ADD CONSTRAINT `ibfk_3` FOREIGN KEY (`id_parent`) REFERENCES `options` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `ibfk_4` FOREIGN KEY (`id`) REFERENCES `history_uids` (`uid`) ON DELETE CASCADE ON UPDATE CASCADE;
mysql sql
asked Nov 21 '18 at 19:19
NababNabab
2,0581426
I have this query:
SELECT options.id, options.text,
COUNT(options2.id) AS num_children
FROM options
JOIN history_uids AS uids
ON uids.uid = options.id
LEFT JOIN options AS options2
ON (options.id = options2.id_parent)
LEFT JOIN history_uids AS uids1
ON uids1.uid = options2.id
WHERE (
options.id = 25
AND (uids1.active = 1 OR
options2.id IS NULL) # Problem
)
GROUP BY `options`.`id`
There is an options table, and another, history_uids, which has a column named uid with the id of every options, and an active column set to 1 or 0.
I am expecting to get a result with:
- The ID and the text of the option which has the
id25, and which hasactiveset to 1 inhistory_uids. - The number of options which have an
id_parentequal to theid(25) and for whichactiveinhistory_uidsis set to 1
So whatever is this number I should get my row if it has active set to 1. I cannot understand how to achieve this: I wanna set as last condition uids1.active = 1 OR "options2 doesn't exist", but to get this I should have my active condition in the ON of options2, which is not possible because at that moment the table history_uids is not yet referenced...
In my case the row has active set to 1 and has 5 children with active set to 0, so I should get my row with num_children set at 0. Instead whatever combination I do in the JOIN or the WHERE I get either num_children set to 5 or no result at all.
Thanks for your help (and for reading!)
Here is the full structure and data for testing:
CREATE TABLE `history_uids` (
`uid` int(10) NOT NULL,
`active` tinyint(1) NOT NULL DEFAULT '1'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `history_uids` (`uid`, `active`) VALUES
(1, 1),
(2, 0),
(3, 0),
(4, 0),
(5, 0),
(6, 0);
CREATE TABLE `options` (
`id` int(10) NOT NULL,
`id_parent` int(10) DEFAULT NULL,
`text` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `options` (`id`, `id_parent`, `text`) VALUES
(1, NULL, 'parent active'),
(2, 1, 'child 1 inactive'),
(3, 1, 'child 2 inactive'),
(4, 1, 'child 3 inactive'),
(5, 1, 'child 4 inactive'),
(6, 1, 'child 5 inactive');
ALTER TABLE `history_uids`
ADD PRIMARY KEY (`uid`);
ALTER TABLE `options`
ADD PRIMARY KEY (`id`),
ADD KEY `id_parent` (`id_parent`);
ALTER TABLE `options`
ADD CONSTRAINT `ibfk_3` FOREIGN KEY (`id_parent`) REFERENCES `options` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `ibfk_4` FOREIGN KEY (`id`) REFERENCES `history_uids` (`uid`) ON DELETE CASCADE ON UPDATE CASCADE;
mysql sql
mysql sql
asked Nov 21 '18 at 19:19
NababNabab
2,0581426
asked Nov 21 '18 at 19:19
NababNabab
2,0581426
edited Nov 21 '18 at 20:04
Nabab
asked Nov 21 '18 at 19:19
NababNabab
2,0581426
asked Nov 21 '18 at 19:19
NababNabab
2,0581426
asked Nov 21 '18 at 19:19
NababNabab
2,0581426
2,0581426
See meta.stackoverflow.com/questions/333952/…
– Strawberry
Nov 21 '18 at 19:24
1
COUNT(options2.id)only counts non-null values, so the rows whereoptions2.id IS NULLwon't be counted.
– Barmar
Nov 21 '18 at 19:48
Can you show some example input and the desired result? I suspect what you need is two queries that you combine withUNION, but it's hard to understand your description.
– Barmar
Nov 21 '18 at 19:50
I have added structure and data if you want to test the query. The goal is to get the row withnum_childrenset at0(as all child options have the correspondinghistory_uids.activeset to 0)
– Nabab
Nov 21 '18 at 19:59
@Barmar I would like this as a result:{id: 1, text: 'parent active', num_children: 0}
– Nabab
Nov 21 '18 at 22:06
add a comment |
See meta.stackoverflow.com/questions/333952/…
– Strawberry
Nov 21 '18 at 19:24
1
COUNT(options2.id)only counts non-null values, so the rows whereoptions2.id IS NULLwon't be counted.
– Barmar
Nov 21 '18 at 19:48
Can you show some example input and the desired result? I suspect what you need is two queries that you combine withUNION, but it's hard to understand your description.
– Barmar
Nov 21 '18 at 19:50
I have added structure and data if you want to test the query. The goal is to get the row withnum_childrenset at0(as all child options have the correspondinghistory_uids.activeset to 0)
– Nabab
Nov 21 '18 at 19:59
@Barmar I would like this as a result:{id: 1, text: 'parent active', num_children: 0}
– Nabab
Nov 21 '18 at 22:06
See meta.stackoverflow.com/questions/333952/…
– Strawberry
Nov 21 '18 at 19:24
See meta.stackoverflow.com/questions/333952/…
– Strawberry
Nov 21 '18 at 19:24
1
1
COUNT(options2.id) only counts non-null values, so the rows where options2.id IS NULL won't be counted.– Barmar
Nov 21 '18 at 19:48
COUNT(options2.id) only counts non-null values, so the rows where options2.id IS NULL won't be counted.– Barmar
Nov 21 '18 at 19:48
Can you show some example input and the desired result? I suspect what you need is two queries that you combine with
UNION, but it's hard to understand your description.– Barmar
Nov 21 '18 at 19:50
Can you show some example input and the desired result? I suspect what you need is two queries that you combine with
UNION, but it's hard to understand your description.– Barmar
Nov 21 '18 at 19:50
I have added structure and data if you want to test the query. The goal is to get the row with
num_children set at 0 (as all child options have the corresponding history_uids.active set to 0)– Nabab
Nov 21 '18 at 19:59
I have added structure and data if you want to test the query. The goal is to get the row with
num_children set at 0 (as all child options have the corresponding history_uids.active set to 0)– Nabab
Nov 21 '18 at 19:59
@Barmar I would like this as a result:
{id: 1, text: 'parent active', num_children: 0}– Nabab
Nov 21 '18 at 22:06
@Barmar I would like this as a result:
{id: 1, text: 'parent active', num_children: 0}– Nabab
Nov 21 '18 at 22:06
add a comment |
1 Answer
1
active
oldest
votes
You need to put the active test for the child row into the ON clause, so that inactive rows will be filtered out of the LEFT JOIN. And you should be counting uids1.uid, not options2.id, since the inactive rows aren't filtered out until you join with uids1.
SELECT options.id, options.text,
COUNT(uids1.uid) AS num_children
FROM options
JOIN history_uids AS uids
ON uids.uid = options.id
LEFT JOIN options AS options2
ON (options.id = options2.id_parent)
LEFT JOIN history_uids AS uids1
ON uids1.uid = options2.id and uids1.active = 1
WHERE
uids.active = 1
GROUP BY `options`.`id`
DEMO
answered Nov 21 '18 at 22:22
BarmarBarmar
434k36259362
Thanks a lot, I wanted to keep my count as it was but your solution counting the uids helped me found what I wanted: I added another join on the same table before and counted my original join with your last condition:ON uids1.uid = options2.id and uids1.active = 1
– Nabab
Nov 22 '18 at 4:02
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%2f53419145%2fcounting-a-left-join-depending-on-other-left-join%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
You need to put the active test for the child row into the ON clause, so that inactive rows will be filtered out of the LEFT JOIN. And you should be counting uids1.uid, not options2.id, since the inactive rows aren't filtered out until you join with uids1.
SELECT options.id, options.text,
COUNT(uids1.uid) AS num_children
FROM options
JOIN history_uids AS uids
ON uids.uid = options.id
LEFT JOIN options AS options2
ON (options.id = options2.id_parent)
LEFT JOIN history_uids AS uids1
ON uids1.uid = options2.id and uids1.active = 1
WHERE
uids.active = 1
GROUP BY `options`.`id`
DEMO
answered Nov 21 '18 at 22:22
BarmarBarmar
434k36259362
Thanks a lot, I wanted to keep my count as it was but your solution counting the uids helped me found what I wanted: I added another join on the same table before and counted my original join with your last condition:ON uids1.uid = options2.id and uids1.active = 1
– Nabab
Nov 22 '18 at 4:02
add a comment |
You need to put the active test for the child row into the ON clause, so that inactive rows will be filtered out of the LEFT JOIN. And you should be counting uids1.uid, not options2.id, since the inactive rows aren't filtered out until you join with uids1.
SELECT options.id, options.text,
COUNT(uids1.uid) AS num_children
FROM options
JOIN history_uids AS uids
ON uids.uid = options.id
LEFT JOIN options AS options2
ON (options.id = options2.id_parent)
LEFT JOIN history_uids AS uids1
ON uids1.uid = options2.id and uids1.active = 1
WHERE
uids.active = 1
GROUP BY `options`.`id`
DEMO
answered Nov 21 '18 at 22:22
BarmarBarmar
434k36259362
Thanks a lot, I wanted to keep my count as it was but your solution counting the uids helped me found what I wanted: I added another join on the same table before and counted my original join with your last condition:ON uids1.uid = options2.id and uids1.active = 1
– Nabab
Nov 22 '18 at 4:02
add a comment |
You need to put the active test for the child row into the ON clause, so that inactive rows will be filtered out of the LEFT JOIN. And you should be counting uids1.uid, not options2.id, since the inactive rows aren't filtered out until you join with uids1.
SELECT options.id, options.text,
COUNT(uids1.uid) AS num_children
FROM options
JOIN history_uids AS uids
ON uids.uid = options.id
LEFT JOIN options AS options2
ON (options.id = options2.id_parent)
LEFT JOIN history_uids AS uids1
ON uids1.uid = options2.id and uids1.active = 1
WHERE
uids.active = 1
GROUP BY `options`.`id`
DEMO
answered Nov 21 '18 at 22:22
BarmarBarmar
434k36259362
You need to put the active test for the child row into the ON clause, so that inactive rows will be filtered out of the LEFT JOIN. And you should be counting uids1.uid, not options2.id, since the inactive rows aren't filtered out until you join with uids1.
SELECT options.id, options.text,
COUNT(uids1.uid) AS num_children
FROM options
JOIN history_uids AS uids
ON uids.uid = options.id
LEFT JOIN options AS options2
ON (options.id = options2.id_parent)
LEFT JOIN history_uids AS uids1
ON uids1.uid = options2.id and uids1.active = 1
WHERE
uids.active = 1
GROUP BY `options`.`id`
DEMO
answered Nov 21 '18 at 22:22
BarmarBarmar
434k36259362
answered Nov 21 '18 at 22:22
BarmarBarmar
434k36259362
answered Nov 21 '18 at 22:22
BarmarBarmar
434k36259362
answered Nov 21 '18 at 22:22
BarmarBarmar
434k36259362
434k36259362
Thanks a lot, I wanted to keep my count as it was but your solution counting the uids helped me found what I wanted: I added another join on the same table before and counted my original join with your last condition:ON uids1.uid = options2.id and uids1.active = 1
– Nabab
Nov 22 '18 at 4:02
add a comment |
Thanks a lot, I wanted to keep my count as it was but your solution counting the uids helped me found what I wanted: I added another join on the same table before and counted my original join with your last condition:ON uids1.uid = options2.id and uids1.active = 1
– Nabab
Nov 22 '18 at 4:02
Thanks a lot, I wanted to keep my count as it was but your solution counting the uids helped me found what I wanted: I added another join on the same table before and counted my original join with your last condition:
ON uids1.uid = options2.id and uids1.active = 1– Nabab
Nov 22 '18 at 4:02
Thanks a lot, I wanted to keep my count as it was but your solution counting the uids helped me found what I wanted: I added another join on the same table before and counted my original join with your last condition:
ON uids1.uid = options2.id and uids1.active = 1– Nabab
Nov 22 '18 at 4:02
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%2f53419145%2fcounting-a-left-join-depending-on-other-left-join%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
See meta.stackoverflow.com/questions/333952/…
– Strawberry
Nov 21 '18 at 19:24
1
COUNT(options2.id)only counts non-null values, so the rows whereoptions2.id IS NULLwon't be counted.– Barmar
Nov 21 '18 at 19:48
Can you show some example input and the desired result? I suspect what you need is two queries that you combine with
UNION, but it's hard to understand your description.– Barmar
Nov 21 '18 at 19:50
I have added structure and data if you want to test the query. The goal is to get the row with
num_childrenset at0(as all child options have the correspondinghistory_uids.activeset to 0)– Nabab
Nov 21 '18 at 19:59
@Barmar I would like this as a result:
{id: 1, text: 'parent active', num_children: 0}– Nabab
Nov 21 '18 at 22:06