SQL querying a customer ID who ordered both product A and B
Having a bit of trouble when trying to figure out how to return a query of a customer who ordered both A and B
What I'm looking for is all customers who order both product A and product B
sql sqlite self-join
add a comment |
Having a bit of trouble when trying to figure out how to return a query of a customer who ordered both A and B
What I'm looking for is all customers who order both product A and product B
sql sqlite self-join
add a comment |
Having a bit of trouble when trying to figure out how to return a query of a customer who ordered both A and B
What I'm looking for is all customers who order both product A and product B
sql sqlite self-join
Having a bit of trouble when trying to figure out how to return a query of a customer who ordered both A and B
What I'm looking for is all customers who order both product A and product B
sql sqlite self-join
sql sqlite self-join
asked Nov 12 '18 at 19:28
Gus
535
535
add a comment |
add a comment |
5 Answers
5
active
oldest
votes
SELECT CustomerID
FROM table
WHERE product in ('a','b')
GROUP BY customerid
HAVING COUNT(distinct product) = 2
I don't normally post code only answers but there isn't a lot that words can add to this- the query predominantly explains itself
You can also
HAVING max(product) <> min(product)
It may be worth pointing out that in queries, the WHERE is performed, filtering to just products A and B. Then the GROUP BY is performed, grouping customer and counting the distinct number of products (or getting the min and max). Then the HAVING is performed, filtering to just those with 2 distinct products (or getting only those where MIN i.e. A, is different to MAX i.e. B)
If you'v never encountered HAVING, it is logically equivalent to:
SELECT CustomerID
FROM(
SELECT CustomerID, COUNT(distinct product) as count_distinct_product
FROM table
WHERE product in ('a','b')
GROUP BY customerid
)z
WHERE
z.count_distinct_product = 2
In a HAVING clause you can only refer to columns that are mentioned in the group by. You can also refer to aggregate operations (such as count/min/max) on other columns not mentioned in the group by
you got it correct I missed the trick @Caius Jard even I was about to reach the same :) count(distinct product) did the trick
– Himanshu Ahuja
Nov 12 '18 at 19:54
Isn't this assuming that there will only be one and only one record with customer X and product 'A' and one and only one record with customer X and product 'B' ?
– JPortillo
Nov 12 '18 at 20:06
add a comment |
I have never worked with SQLLite, but since it's specs say it is a Relational Database, it should allow the following query.
select CustomerID
from table t
where exists (
select *
from table
where CustomerID = t.CustomerID
and Product = 'A'
)
and exists (
select *
from table
where CustomerID = t.CustomerID
and Product = 'B'
)
add a comment |
I'd use a correlated sub-query with a HAVING
clause to scoop in both products in a single WHERE
clause.
SELECT
t.Customer
FROM
@t AS t
WHERE
EXISTS
(
SELECT
1
FROM
@t AS s
WHERE
t.Customer = s.Customer
AND s.Product IN ('A', 'B')
HAVING
COUNT(DISTINCT s.Product) = 2
)
GROUP BY
t.Customer;
Why so complex?
– Caius Jard
Nov 12 '18 at 19:52
@CaiusJard It's not really that complex...but your answer is better. :)
– Eric Brandt
Nov 12 '18 at 20:21
add a comment |
Select customerid from table group by customerid having product like 'A' and product like 'B'
or
you can try having count(distinct product) =2
this seems to be more accurate.
The whole idea is in a group of customerid suppose 1 if I have several A's and B's count(distinct product) will give as 2 else it will be 1 so the answer is as above.
Can you please check with having count(distinct product) as in my second query and tell me if its working.
– Himanshu Ahuja
Nov 12 '18 at 20:02
add a comment |
Another way I just figured out was
SELECT CustomerID
FROM table
WHERE product in ('a','b')
GROUP BY customerid
HAVING sum(case product ='a' then 1 else 0 end) > 0
and sum(case when product ='b' then 1 else 0 end) > 0
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%2f53268844%2fsql-querying-a-customer-id-who-ordered-both-product-a-and-b%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
5 Answers
5
active
oldest
votes
5 Answers
5
active
oldest
votes
active
oldest
votes
active
oldest
votes
SELECT CustomerID
FROM table
WHERE product in ('a','b')
GROUP BY customerid
HAVING COUNT(distinct product) = 2
I don't normally post code only answers but there isn't a lot that words can add to this- the query predominantly explains itself
You can also
HAVING max(product) <> min(product)
It may be worth pointing out that in queries, the WHERE is performed, filtering to just products A and B. Then the GROUP BY is performed, grouping customer and counting the distinct number of products (or getting the min and max). Then the HAVING is performed, filtering to just those with 2 distinct products (or getting only those where MIN i.e. A, is different to MAX i.e. B)
If you'v never encountered HAVING, it is logically equivalent to:
SELECT CustomerID
FROM(
SELECT CustomerID, COUNT(distinct product) as count_distinct_product
FROM table
WHERE product in ('a','b')
GROUP BY customerid
)z
WHERE
z.count_distinct_product = 2
In a HAVING clause you can only refer to columns that are mentioned in the group by. You can also refer to aggregate operations (such as count/min/max) on other columns not mentioned in the group by
you got it correct I missed the trick @Caius Jard even I was about to reach the same :) count(distinct product) did the trick
– Himanshu Ahuja
Nov 12 '18 at 19:54
Isn't this assuming that there will only be one and only one record with customer X and product 'A' and one and only one record with customer X and product 'B' ?
– JPortillo
Nov 12 '18 at 20:06
add a comment |
SELECT CustomerID
FROM table
WHERE product in ('a','b')
GROUP BY customerid
HAVING COUNT(distinct product) = 2
I don't normally post code only answers but there isn't a lot that words can add to this- the query predominantly explains itself
You can also
HAVING max(product) <> min(product)
It may be worth pointing out that in queries, the WHERE is performed, filtering to just products A and B. Then the GROUP BY is performed, grouping customer and counting the distinct number of products (or getting the min and max). Then the HAVING is performed, filtering to just those with 2 distinct products (or getting only those where MIN i.e. A, is different to MAX i.e. B)
If you'v never encountered HAVING, it is logically equivalent to:
SELECT CustomerID
FROM(
SELECT CustomerID, COUNT(distinct product) as count_distinct_product
FROM table
WHERE product in ('a','b')
GROUP BY customerid
)z
WHERE
z.count_distinct_product = 2
In a HAVING clause you can only refer to columns that are mentioned in the group by. You can also refer to aggregate operations (such as count/min/max) on other columns not mentioned in the group by
you got it correct I missed the trick @Caius Jard even I was about to reach the same :) count(distinct product) did the trick
– Himanshu Ahuja
Nov 12 '18 at 19:54
Isn't this assuming that there will only be one and only one record with customer X and product 'A' and one and only one record with customer X and product 'B' ?
– JPortillo
Nov 12 '18 at 20:06
add a comment |
SELECT CustomerID
FROM table
WHERE product in ('a','b')
GROUP BY customerid
HAVING COUNT(distinct product) = 2
I don't normally post code only answers but there isn't a lot that words can add to this- the query predominantly explains itself
You can also
HAVING max(product) <> min(product)
It may be worth pointing out that in queries, the WHERE is performed, filtering to just products A and B. Then the GROUP BY is performed, grouping customer and counting the distinct number of products (or getting the min and max). Then the HAVING is performed, filtering to just those with 2 distinct products (or getting only those where MIN i.e. A, is different to MAX i.e. B)
If you'v never encountered HAVING, it is logically equivalent to:
SELECT CustomerID
FROM(
SELECT CustomerID, COUNT(distinct product) as count_distinct_product
FROM table
WHERE product in ('a','b')
GROUP BY customerid
)z
WHERE
z.count_distinct_product = 2
In a HAVING clause you can only refer to columns that are mentioned in the group by. You can also refer to aggregate operations (such as count/min/max) on other columns not mentioned in the group by
SELECT CustomerID
FROM table
WHERE product in ('a','b')
GROUP BY customerid
HAVING COUNT(distinct product) = 2
I don't normally post code only answers but there isn't a lot that words can add to this- the query predominantly explains itself
You can also
HAVING max(product) <> min(product)
It may be worth pointing out that in queries, the WHERE is performed, filtering to just products A and B. Then the GROUP BY is performed, grouping customer and counting the distinct number of products (or getting the min and max). Then the HAVING is performed, filtering to just those with 2 distinct products (or getting only those where MIN i.e. A, is different to MAX i.e. B)
If you'v never encountered HAVING, it is logically equivalent to:
SELECT CustomerID
FROM(
SELECT CustomerID, COUNT(distinct product) as count_distinct_product
FROM table
WHERE product in ('a','b')
GROUP BY customerid
)z
WHERE
z.count_distinct_product = 2
In a HAVING clause you can only refer to columns that are mentioned in the group by. You can also refer to aggregate operations (such as count/min/max) on other columns not mentioned in the group by
edited Nov 12 '18 at 20:03
answered Nov 12 '18 at 19:50
Caius Jard
10.3k21137
10.3k21137
you got it correct I missed the trick @Caius Jard even I was about to reach the same :) count(distinct product) did the trick
– Himanshu Ahuja
Nov 12 '18 at 19:54
Isn't this assuming that there will only be one and only one record with customer X and product 'A' and one and only one record with customer X and product 'B' ?
– JPortillo
Nov 12 '18 at 20:06
add a comment |
you got it correct I missed the trick @Caius Jard even I was about to reach the same :) count(distinct product) did the trick
– Himanshu Ahuja
Nov 12 '18 at 19:54
Isn't this assuming that there will only be one and only one record with customer X and product 'A' and one and only one record with customer X and product 'B' ?
– JPortillo
Nov 12 '18 at 20:06
you got it correct I missed the trick @Caius Jard even I was about to reach the same :) count(distinct product) did the trick
– Himanshu Ahuja
Nov 12 '18 at 19:54
you got it correct I missed the trick @Caius Jard even I was about to reach the same :) count(distinct product) did the trick
– Himanshu Ahuja
Nov 12 '18 at 19:54
Isn't this assuming that there will only be one and only one record with customer X and product 'A' and one and only one record with customer X and product 'B' ?
– JPortillo
Nov 12 '18 at 20:06
Isn't this assuming that there will only be one and only one record with customer X and product 'A' and one and only one record with customer X and product 'B' ?
– JPortillo
Nov 12 '18 at 20:06
add a comment |
I have never worked with SQLLite, but since it's specs say it is a Relational Database, it should allow the following query.
select CustomerID
from table t
where exists (
select *
from table
where CustomerID = t.CustomerID
and Product = 'A'
)
and exists (
select *
from table
where CustomerID = t.CustomerID
and Product = 'B'
)
add a comment |
I have never worked with SQLLite, but since it's specs say it is a Relational Database, it should allow the following query.
select CustomerID
from table t
where exists (
select *
from table
where CustomerID = t.CustomerID
and Product = 'A'
)
and exists (
select *
from table
where CustomerID = t.CustomerID
and Product = 'B'
)
add a comment |
I have never worked with SQLLite, but since it's specs say it is a Relational Database, it should allow the following query.
select CustomerID
from table t
where exists (
select *
from table
where CustomerID = t.CustomerID
and Product = 'A'
)
and exists (
select *
from table
where CustomerID = t.CustomerID
and Product = 'B'
)
I have never worked with SQLLite, but since it's specs say it is a Relational Database, it should allow the following query.
select CustomerID
from table t
where exists (
select *
from table
where CustomerID = t.CustomerID
and Product = 'A'
)
and exists (
select *
from table
where CustomerID = t.CustomerID
and Product = 'B'
)
answered Nov 12 '18 at 19:39
JPortillo
675
675
add a comment |
add a comment |
I'd use a correlated sub-query with a HAVING
clause to scoop in both products in a single WHERE
clause.
SELECT
t.Customer
FROM
@t AS t
WHERE
EXISTS
(
SELECT
1
FROM
@t AS s
WHERE
t.Customer = s.Customer
AND s.Product IN ('A', 'B')
HAVING
COUNT(DISTINCT s.Product) = 2
)
GROUP BY
t.Customer;
Why so complex?
– Caius Jard
Nov 12 '18 at 19:52
@CaiusJard It's not really that complex...but your answer is better. :)
– Eric Brandt
Nov 12 '18 at 20:21
add a comment |
I'd use a correlated sub-query with a HAVING
clause to scoop in both products in a single WHERE
clause.
SELECT
t.Customer
FROM
@t AS t
WHERE
EXISTS
(
SELECT
1
FROM
@t AS s
WHERE
t.Customer = s.Customer
AND s.Product IN ('A', 'B')
HAVING
COUNT(DISTINCT s.Product) = 2
)
GROUP BY
t.Customer;
Why so complex?
– Caius Jard
Nov 12 '18 at 19:52
@CaiusJard It's not really that complex...but your answer is better. :)
– Eric Brandt
Nov 12 '18 at 20:21
add a comment |
I'd use a correlated sub-query with a HAVING
clause to scoop in both products in a single WHERE
clause.
SELECT
t.Customer
FROM
@t AS t
WHERE
EXISTS
(
SELECT
1
FROM
@t AS s
WHERE
t.Customer = s.Customer
AND s.Product IN ('A', 'B')
HAVING
COUNT(DISTINCT s.Product) = 2
)
GROUP BY
t.Customer;
I'd use a correlated sub-query with a HAVING
clause to scoop in both products in a single WHERE
clause.
SELECT
t.Customer
FROM
@t AS t
WHERE
EXISTS
(
SELECT
1
FROM
@t AS s
WHERE
t.Customer = s.Customer
AND s.Product IN ('A', 'B')
HAVING
COUNT(DISTINCT s.Product) = 2
)
GROUP BY
t.Customer;
answered Nov 12 '18 at 19:48
Eric Brandt
2,2721624
2,2721624
Why so complex?
– Caius Jard
Nov 12 '18 at 19:52
@CaiusJard It's not really that complex...but your answer is better. :)
– Eric Brandt
Nov 12 '18 at 20:21
add a comment |
Why so complex?
– Caius Jard
Nov 12 '18 at 19:52
@CaiusJard It's not really that complex...but your answer is better. :)
– Eric Brandt
Nov 12 '18 at 20:21
Why so complex?
– Caius Jard
Nov 12 '18 at 19:52
Why so complex?
– Caius Jard
Nov 12 '18 at 19:52
@CaiusJard It's not really that complex...but your answer is better. :)
– Eric Brandt
Nov 12 '18 at 20:21
@CaiusJard It's not really that complex...but your answer is better. :)
– Eric Brandt
Nov 12 '18 at 20:21
add a comment |
Select customerid from table group by customerid having product like 'A' and product like 'B'
or
you can try having count(distinct product) =2
this seems to be more accurate.
The whole idea is in a group of customerid suppose 1 if I have several A's and B's count(distinct product) will give as 2 else it will be 1 so the answer is as above.
Can you please check with having count(distinct product) as in my second query and tell me if its working.
– Himanshu Ahuja
Nov 12 '18 at 20:02
add a comment |
Select customerid from table group by customerid having product like 'A' and product like 'B'
or
you can try having count(distinct product) =2
this seems to be more accurate.
The whole idea is in a group of customerid suppose 1 if I have several A's and B's count(distinct product) will give as 2 else it will be 1 so the answer is as above.
Can you please check with having count(distinct product) as in my second query and tell me if its working.
– Himanshu Ahuja
Nov 12 '18 at 20:02
add a comment |
Select customerid from table group by customerid having product like 'A' and product like 'B'
or
you can try having count(distinct product) =2
this seems to be more accurate.
The whole idea is in a group of customerid suppose 1 if I have several A's and B's count(distinct product) will give as 2 else it will be 1 so the answer is as above.
Select customerid from table group by customerid having product like 'A' and product like 'B'
or
you can try having count(distinct product) =2
this seems to be more accurate.
The whole idea is in a group of customerid suppose 1 if I have several A's and B's count(distinct product) will give as 2 else it will be 1 so the answer is as above.
edited Nov 12 '18 at 19:56
answered Nov 12 '18 at 19:47
Himanshu Ahuja
584216
584216
Can you please check with having count(distinct product) as in my second query and tell me if its working.
– Himanshu Ahuja
Nov 12 '18 at 20:02
add a comment |
Can you please check with having count(distinct product) as in my second query and tell me if its working.
– Himanshu Ahuja
Nov 12 '18 at 20:02
Can you please check with having count(distinct product) as in my second query and tell me if its working.
– Himanshu Ahuja
Nov 12 '18 at 20:02
Can you please check with having count(distinct product) as in my second query and tell me if its working.
– Himanshu Ahuja
Nov 12 '18 at 20:02
add a comment |
Another way I just figured out was
SELECT CustomerID
FROM table
WHERE product in ('a','b')
GROUP BY customerid
HAVING sum(case product ='a' then 1 else 0 end) > 0
and sum(case when product ='b' then 1 else 0 end) > 0
add a comment |
Another way I just figured out was
SELECT CustomerID
FROM table
WHERE product in ('a','b')
GROUP BY customerid
HAVING sum(case product ='a' then 1 else 0 end) > 0
and sum(case when product ='b' then 1 else 0 end) > 0
add a comment |
Another way I just figured out was
SELECT CustomerID
FROM table
WHERE product in ('a','b')
GROUP BY customerid
HAVING sum(case product ='a' then 1 else 0 end) > 0
and sum(case when product ='b' then 1 else 0 end) > 0
Another way I just figured out was
SELECT CustomerID
FROM table
WHERE product in ('a','b')
GROUP BY customerid
HAVING sum(case product ='a' then 1 else 0 end) > 0
and sum(case when product ='b' then 1 else 0 end) > 0
answered Nov 12 '18 at 20:03
Gus
535
535
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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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%2f53268844%2fsql-querying-a-customer-id-who-ordered-both-product-a-and-b%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