Choosing a column that fulfills many conditions in different records
I have got table like this:
+----------+---------+
| Customer | Product |
+----------+---------+
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 1 |
| 2 | 2 |
| 2 | 2 |
+----------+---------+
I would like to select Customer who bought Products 1 AND 2 AND 3. So my query should return 1. How to achieve that?
mysql select
asked Nov 16 '18 at 8:59
jimmy winstonejimmy winstone
387
|
show 1 more comment
I have got table like this:
+----------+---------+
| Customer | Product |
+----------+---------+
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 1 |
| 2 | 2 |
| 2 | 2 |
+----------+---------+
I would like to select Customer who bought Products 1 AND 2 AND 3. So my query should return 1. How to achieve that?
mysql select
asked Nov 16 '18 at 8:59
jimmy winstonejimmy winstone
387
Can there be more types of product(s), other than 1,2,3 ?
– Madhur Bhaiya
Nov 16 '18 at 9:07
Question is completely simplified to my problem, but yes it can, and lets say I get products from many subqueries
– jimmy winstone
Nov 16 '18 at 9:13
So basically two cases are possible. Case 1: Get those customers which have purchased only 1,2,3 products and each one of them has been purchased atleast once. Case 2: Get those customers which have purchase each one of the 1,2,3 atleast (they may have purchased other products as well). Which case are you looking the solution for ?
– Madhur Bhaiya
Nov 16 '18 at 9:17
Definitely the second solution, I need those who have at least once bought each of these products.
– jimmy winstone
Nov 16 '18 at 9:21
1
Yes, your solution is really close to my problem, but instead of SUM(Product = 1) i put subqueries over there. Thank you :)
– jimmy winstone
Nov 16 '18 at 9:35
|
show 1 more comment
I have got table like this:
+----------+---------+
| Customer | Product |
+----------+---------+
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 1 |
| 2 | 2 |
| 2 | 2 |
+----------+---------+
I would like to select Customer who bought Products 1 AND 2 AND 3. So my query should return 1. How to achieve that?
mysql select
asked Nov 16 '18 at 8:59
jimmy winstonejimmy winstone
387
I have got table like this:
+----------+---------+
| Customer | Product |
+----------+---------+
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 1 |
| 2 | 2 |
| 2 | 2 |
+----------+---------+
I would like to select Customer who bought Products 1 AND 2 AND 3. So my query should return 1. How to achieve that?
mysql select
mysql select
asked Nov 16 '18 at 8:59
jimmy winstonejimmy winstone
387
asked Nov 16 '18 at 8:59
jimmy winstonejimmy winstone
387
asked Nov 16 '18 at 8:59
jimmy winstonejimmy winstone
387
asked Nov 16 '18 at 8:59
jimmy winstonejimmy winstone
387
asked Nov 16 '18 at 8:59
jimmy winstonejimmy winstone
387
387
Can there be more types of product(s), other than 1,2,3 ?
– Madhur Bhaiya
Nov 16 '18 at 9:07
Question is completely simplified to my problem, but yes it can, and lets say I get products from many subqueries
– jimmy winstone
Nov 16 '18 at 9:13
So basically two cases are possible. Case 1: Get those customers which have purchased only 1,2,3 products and each one of them has been purchased atleast once. Case 2: Get those customers which have purchase each one of the 1,2,3 atleast (they may have purchased other products as well). Which case are you looking the solution for ?
– Madhur Bhaiya
Nov 16 '18 at 9:17
Definitely the second solution, I need those who have at least once bought each of these products.
– jimmy winstone
Nov 16 '18 at 9:21
1
Yes, your solution is really close to my problem, but instead of SUM(Product = 1) i put subqueries over there. Thank you :)
– jimmy winstone
Nov 16 '18 at 9:35
|
show 1 more comment
Can there be more types of product(s), other than 1,2,3 ?
– Madhur Bhaiya
Nov 16 '18 at 9:07
Question is completely simplified to my problem, but yes it can, and lets say I get products from many subqueries
– jimmy winstone
Nov 16 '18 at 9:13
So basically two cases are possible. Case 1: Get those customers which have purchased only 1,2,3 products and each one of them has been purchased atleast once. Case 2: Get those customers which have purchase each one of the 1,2,3 atleast (they may have purchased other products as well). Which case are you looking the solution for ?
– Madhur Bhaiya
Nov 16 '18 at 9:17
Definitely the second solution, I need those who have at least once bought each of these products.
– jimmy winstone
Nov 16 '18 at 9:21
1
Yes, your solution is really close to my problem, but instead of SUM(Product = 1) i put subqueries over there. Thank you :)
– jimmy winstone
Nov 16 '18 at 9:35
Can there be more types of product(s), other than 1,2,3 ?
– Madhur Bhaiya
Nov 16 '18 at 9:07
Can there be more types of product(s), other than 1,2,3 ?
– Madhur Bhaiya
Nov 16 '18 at 9:07
Question is completely simplified to my problem, but yes it can, and lets say I get products from many subqueries
– jimmy winstone
Nov 16 '18 at 9:13
Question is completely simplified to my problem, but yes it can, and lets say I get products from many subqueries
– jimmy winstone
Nov 16 '18 at 9:13
So basically two cases are possible. Case 1: Get those customers which have purchased only 1,2,3 products and each one of them has been purchased atleast once. Case 2: Get those customers which have purchase each one of the 1,2,3 atleast (they may have purchased other products as well). Which case are you looking the solution for ?
– Madhur Bhaiya
Nov 16 '18 at 9:17
So basically two cases are possible. Case 1: Get those customers which have purchased only 1,2,3 products and each one of them has been purchased atleast once. Case 2: Get those customers which have purchase each one of the 1,2,3 atleast (they may have purchased other products as well). Which case are you looking the solution for ?
– Madhur Bhaiya
Nov 16 '18 at 9:17
Definitely the second solution, I need those who have at least once bought each of these products.
– jimmy winstone
Nov 16 '18 at 9:21
Definitely the second solution, I need those who have at least once bought each of these products.
– jimmy winstone
Nov 16 '18 at 9:21
1
1
Yes, your solution is really close to my problem, but instead of SUM(Product = 1) i put subqueries over there. Thank you :)
– jimmy winstone
Nov 16 '18 at 9:35
Yes, your solution is really close to my problem, but instead of SUM(Product = 1) i put subqueries over there. Thank you :)
– jimmy winstone
Nov 16 '18 at 9:35
|
show 1 more comment
2 Answers
2
active
oldest
votes
You can GROUP BY on the Customer and use conditional aggregation based filtering inside the Having clause. MySQL automatically casts boolean values to 0/1 when using in numeric context.
In order to have a specific Product bought by a Customer, its SUM(Product = ..) should be 1.
Case 1: Get those customers which have purchased each one of the 1,2,3 products at-least (they may have purchased other products as well).
SELECT Customer
FROM your_table
GROUP BY Customer
HAVING SUM(Product = 1) AND -- 1 should be bought
SUM(Product = 2) AND -- 2 should be bought
SUM(Product = 3) -- 3 should be bought
If you want exclusivity, i.e., the customer has not bought any other product other than 1,2,3; then you can use the following instead.
Case 2: Get those customers which have purchased only 1,2,3 products and each one of them has been purchased at-least once.
SELECT Customer
FROM your_table
GROUP BY Customer
HAVING SUM(Product = 1) AND -- 1 should be bought
SUM(Product = 2) AND -- 2 should be bought
SUM(Product = 3) AND -- 3 should be bought
NOT SUM(Product NOT IN (1,2,3)) -- anything other 1,2,3 should not be bought
answered Nov 16 '18 at 9:01
Madhur BhaiyaMadhur Bhaiya
19.6k62236
add a comment |
if you want the customer who bought all the 3 product you could use aggregation function count(distinct product)
SELECT Customer
FROM your_table
where product in (1,2,3)
GROUP BY Customer
HAVING count(distinct product) = 3
answered Nov 16 '18 at 9:04
scaisEdgescaisEdge
96.8k105272
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%2f53334460%2fchoosing-a-column-that-fulfills-many-conditions-in-different-records%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
You can GROUP BY on the Customer and use conditional aggregation based filtering inside the Having clause. MySQL automatically casts boolean values to 0/1 when using in numeric context.
In order to have a specific Product bought by a Customer, its SUM(Product = ..) should be 1.
Case 1: Get those customers which have purchased each one of the 1,2,3 products at-least (they may have purchased other products as well).
SELECT Customer
FROM your_table
GROUP BY Customer
HAVING SUM(Product = 1) AND -- 1 should be bought
SUM(Product = 2) AND -- 2 should be bought
SUM(Product = 3) -- 3 should be bought
If you want exclusivity, i.e., the customer has not bought any other product other than 1,2,3; then you can use the following instead.
Case 2: Get those customers which have purchased only 1,2,3 products and each one of them has been purchased at-least once.
SELECT Customer
FROM your_table
GROUP BY Customer
HAVING SUM(Product = 1) AND -- 1 should be bought
SUM(Product = 2) AND -- 2 should be bought
SUM(Product = 3) AND -- 3 should be bought
NOT SUM(Product NOT IN (1,2,3)) -- anything other 1,2,3 should not be bought
answered Nov 16 '18 at 9:01
Madhur BhaiyaMadhur Bhaiya
19.6k62236
add a comment |
You can GROUP BY on the Customer and use conditional aggregation based filtering inside the Having clause. MySQL automatically casts boolean values to 0/1 when using in numeric context.
In order to have a specific Product bought by a Customer, its SUM(Product = ..) should be 1.
Case 1: Get those customers which have purchased each one of the 1,2,3 products at-least (they may have purchased other products as well).
SELECT Customer
FROM your_table
GROUP BY Customer
HAVING SUM(Product = 1) AND -- 1 should be bought
SUM(Product = 2) AND -- 2 should be bought
SUM(Product = 3) -- 3 should be bought
If you want exclusivity, i.e., the customer has not bought any other product other than 1,2,3; then you can use the following instead.
Case 2: Get those customers which have purchased only 1,2,3 products and each one of them has been purchased at-least once.
SELECT Customer
FROM your_table
GROUP BY Customer
HAVING SUM(Product = 1) AND -- 1 should be bought
SUM(Product = 2) AND -- 2 should be bought
SUM(Product = 3) AND -- 3 should be bought
NOT SUM(Product NOT IN (1,2,3)) -- anything other 1,2,3 should not be bought
answered Nov 16 '18 at 9:01
Madhur BhaiyaMadhur Bhaiya
19.6k62236
add a comment |
You can GROUP BY on the Customer and use conditional aggregation based filtering inside the Having clause. MySQL automatically casts boolean values to 0/1 when using in numeric context.
In order to have a specific Product bought by a Customer, its SUM(Product = ..) should be 1.
Case 1: Get those customers which have purchased each one of the 1,2,3 products at-least (they may have purchased other products as well).
SELECT Customer
FROM your_table
GROUP BY Customer
HAVING SUM(Product = 1) AND -- 1 should be bought
SUM(Product = 2) AND -- 2 should be bought
SUM(Product = 3) -- 3 should be bought
If you want exclusivity, i.e., the customer has not bought any other product other than 1,2,3; then you can use the following instead.
Case 2: Get those customers which have purchased only 1,2,3 products and each one of them has been purchased at-least once.
SELECT Customer
FROM your_table
GROUP BY Customer
HAVING SUM(Product = 1) AND -- 1 should be bought
SUM(Product = 2) AND -- 2 should be bought
SUM(Product = 3) AND -- 3 should be bought
NOT SUM(Product NOT IN (1,2,3)) -- anything other 1,2,3 should not be bought
answered Nov 16 '18 at 9:01
Madhur BhaiyaMadhur Bhaiya
19.6k62236
You can GROUP BY on the Customer and use conditional aggregation based filtering inside the Having clause. MySQL automatically casts boolean values to 0/1 when using in numeric context.
In order to have a specific Product bought by a Customer, its SUM(Product = ..) should be 1.
Case 1: Get those customers which have purchased each one of the 1,2,3 products at-least (they may have purchased other products as well).
SELECT Customer
FROM your_table
GROUP BY Customer
HAVING SUM(Product = 1) AND -- 1 should be bought
SUM(Product = 2) AND -- 2 should be bought
SUM(Product = 3) -- 3 should be bought
If you want exclusivity, i.e., the customer has not bought any other product other than 1,2,3; then you can use the following instead.
Case 2: Get those customers which have purchased only 1,2,3 products and each one of them has been purchased at-least once.
SELECT Customer
FROM your_table
GROUP BY Customer
HAVING SUM(Product = 1) AND -- 1 should be bought
SUM(Product = 2) AND -- 2 should be bought
SUM(Product = 3) AND -- 3 should be bought
NOT SUM(Product NOT IN (1,2,3)) -- anything other 1,2,3 should not be bought
answered Nov 16 '18 at 9:01
Madhur BhaiyaMadhur Bhaiya
19.6k62236
edited Nov 16 '18 at 9:22
answered Nov 16 '18 at 9:01
Madhur BhaiyaMadhur Bhaiya
19.6k62236
answered Nov 16 '18 at 9:01
Madhur BhaiyaMadhur Bhaiya
19.6k62236
answered Nov 16 '18 at 9:01
Madhur BhaiyaMadhur Bhaiya
19.6k62236
19.6k62236
add a comment |
add a comment |
if you want the customer who bought all the 3 product you could use aggregation function count(distinct product)
SELECT Customer
FROM your_table
where product in (1,2,3)
GROUP BY Customer
HAVING count(distinct product) = 3
answered Nov 16 '18 at 9:04
scaisEdgescaisEdge
96.8k105272
add a comment |
if you want the customer who bought all the 3 product you could use aggregation function count(distinct product)
SELECT Customer
FROM your_table
where product in (1,2,3)
GROUP BY Customer
HAVING count(distinct product) = 3
answered Nov 16 '18 at 9:04
scaisEdgescaisEdge
96.8k105272
add a comment |
if you want the customer who bought all the 3 product you could use aggregation function count(distinct product)
SELECT Customer
FROM your_table
where product in (1,2,3)
GROUP BY Customer
HAVING count(distinct product) = 3
answered Nov 16 '18 at 9:04
scaisEdgescaisEdge
96.8k105272
if you want the customer who bought all the 3 product you could use aggregation function count(distinct product)
SELECT Customer
FROM your_table
where product in (1,2,3)
GROUP BY Customer
HAVING count(distinct product) = 3
answered Nov 16 '18 at 9:04
scaisEdgescaisEdge
96.8k105272
edited Nov 16 '18 at 9:42
answered Nov 16 '18 at 9:04
scaisEdgescaisEdge
96.8k105272
answered Nov 16 '18 at 9:04
scaisEdgescaisEdge
96.8k105272
answered Nov 16 '18 at 9:04
scaisEdgescaisEdge
96.8k105272
96.8k105272
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%2f53334460%2fchoosing-a-column-that-fulfills-many-conditions-in-different-records%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
Can there be more types of product(s), other than 1,2,3 ?
– Madhur Bhaiya
Nov 16 '18 at 9:07
Question is completely simplified to my problem, but yes it can, and lets say I get products from many subqueries
– jimmy winstone
Nov 16 '18 at 9:13
So basically two cases are possible. Case 1: Get those customers which have purchased only 1,2,3 products and each one of them has been purchased atleast once. Case 2: Get those customers which have purchase each one of the 1,2,3 atleast (they may have purchased other products as well). Which case are you looking the solution for ?
– Madhur Bhaiya
Nov 16 '18 at 9:17
Definitely the second solution, I need those who have at least once bought each of these products.
– jimmy winstone
Nov 16 '18 at 9:21
1
Yes, your solution is really close to my problem, but instead of SUM(Product = 1) i put subqueries over there. Thank you :)
– jimmy winstone
Nov 16 '18 at 9:35