MySQL using CASE WHEN with IN subquery
Here is a brief explanation of what I'm trying to accomplish; my query follows below.
I have two tables, one with 500 products product_limit500, and another with orders order_limit1000.
My goal is to create a table with all 500 products for each order, all in the same order (as in order one followed by all other products, and then order two followed by the products in the same order).
This is the query that I have tried using.
SELECT
r1.order_id,
r1.product_id,
r1.product_name,
CASE WHEN p1.product_id IN (SELECT
r2.product_id
FROM
order_limit1000 r2
WHERE
r2.order_id = r1.order_id) THEN 's'
ELSE '?'
END as 'torf'
FROM
order_limit1000 r1,
product_limit500 p1;
And this is the result as of right now.
order_id product_id product_name torf
5 13176 Bag of Organic Bananas ?
5 13176 Bag of Organic Bananas s
5 13176 Bag of Organic Bananas ?
5 13176 Bag of Organic Bananas ?
5 13176 Bag of Organic Bananas s
5 13176 Bag of Organic Bananas ?
5 13176 Bag of Organic Bananas ?
5 13176 Bag of Organic Bananas ?
5 13176 Bag of Organic Bananas ?
5 13176 Bag of Organic Bananas ?
5 13176 Bag of Organic Bananas s
5 13176 Bag of Organic Bananas ?
...
Thanks for any help you can provide.
...
Right now I'm using a view with 500 products and 1000 rows of orders, I'm still thinking what the final size for orders, probably something like 500k to 1mi rows. But the datset I`m using has something like 4.5mi rows.
The expected result would be something like the following.
order_id product_id product_name torf
5 123 tomatoes s
5 124 limes ?
...
100 123 tomatoes ?
100 124 limes ?
mysql
asked Nov 22 '18 at 2:27
Gustavo AzevedoGustavo Azevedo
33
add a comment |
Here is a brief explanation of what I'm trying to accomplish; my query follows below.
I have two tables, one with 500 products product_limit500, and another with orders order_limit1000.
My goal is to create a table with all 500 products for each order, all in the same order (as in order one followed by all other products, and then order two followed by the products in the same order).
This is the query that I have tried using.
SELECT
r1.order_id,
r1.product_id,
r1.product_name,
CASE WHEN p1.product_id IN (SELECT
r2.product_id
FROM
order_limit1000 r2
WHERE
r2.order_id = r1.order_id) THEN 's'
ELSE '?'
END as 'torf'
FROM
order_limit1000 r1,
product_limit500 p1;
And this is the result as of right now.
order_id product_id product_name torf
5 13176 Bag of Organic Bananas ?
5 13176 Bag of Organic Bananas s
5 13176 Bag of Organic Bananas ?
5 13176 Bag of Organic Bananas ?
5 13176 Bag of Organic Bananas s
5 13176 Bag of Organic Bananas ?
5 13176 Bag of Organic Bananas ?
5 13176 Bag of Organic Bananas ?
5 13176 Bag of Organic Bananas ?
5 13176 Bag of Organic Bananas ?
5 13176 Bag of Organic Bananas s
5 13176 Bag of Organic Bananas ?
...
Thanks for any help you can provide.
...
Right now I'm using a view with 500 products and 1000 rows of orders, I'm still thinking what the final size for orders, probably something like 500k to 1mi rows. But the datset I`m using has something like 4.5mi rows.
The expected result would be something like the following.
order_id product_id product_name torf
5 123 tomatoes s
5 124 limes ?
...
100 123 tomatoes ?
100 124 limes ?
mysql
asked Nov 22 '18 at 2:27
Gustavo AzevedoGustavo Azevedo
33
Can you provide a minimum sample of data and the expected output from it, this will help a lot to understand the goal. Also, add the tables schema.
– Shidersz
Nov 22 '18 at 2:36
Sample data and expected result will be helpful.. And avoid using comma for join.. :)
– dwir182
Nov 22 '18 at 2:37
add a comment |
Here is a brief explanation of what I'm trying to accomplish; my query follows below.
I have two tables, one with 500 products product_limit500, and another with orders order_limit1000.
My goal is to create a table with all 500 products for each order, all in the same order (as in order one followed by all other products, and then order two followed by the products in the same order).
This is the query that I have tried using.
SELECT
r1.order_id,
r1.product_id,
r1.product_name,
CASE WHEN p1.product_id IN (SELECT
r2.product_id
FROM
order_limit1000 r2
WHERE
r2.order_id = r1.order_id) THEN 's'
ELSE '?'
END as 'torf'
FROM
order_limit1000 r1,
product_limit500 p1;
And this is the result as of right now.
order_id product_id product_name torf
5 13176 Bag of Organic Bananas ?
5 13176 Bag of Organic Bananas s
5 13176 Bag of Organic Bananas ?
5 13176 Bag of Organic Bananas ?
5 13176 Bag of Organic Bananas s
5 13176 Bag of Organic Bananas ?
5 13176 Bag of Organic Bananas ?
5 13176 Bag of Organic Bananas ?
5 13176 Bag of Organic Bananas ?
5 13176 Bag of Organic Bananas ?
5 13176 Bag of Organic Bananas s
5 13176 Bag of Organic Bananas ?
...
Thanks for any help you can provide.
...
Right now I'm using a view with 500 products and 1000 rows of orders, I'm still thinking what the final size for orders, probably something like 500k to 1mi rows. But the datset I`m using has something like 4.5mi rows.
The expected result would be something like the following.
order_id product_id product_name torf
5 123 tomatoes s
5 124 limes ?
...
100 123 tomatoes ?
100 124 limes ?
mysql
asked Nov 22 '18 at 2:27
Gustavo AzevedoGustavo Azevedo
33
Here is a brief explanation of what I'm trying to accomplish; my query follows below.
I have two tables, one with 500 products product_limit500, and another with orders order_limit1000.
My goal is to create a table with all 500 products for each order, all in the same order (as in order one followed by all other products, and then order two followed by the products in the same order).
This is the query that I have tried using.
SELECT
r1.order_id,
r1.product_id,
r1.product_name,
CASE WHEN p1.product_id IN (SELECT
r2.product_id
FROM
order_limit1000 r2
WHERE
r2.order_id = r1.order_id) THEN 's'
ELSE '?'
END as 'torf'
FROM
order_limit1000 r1,
product_limit500 p1;
And this is the result as of right now.
order_id product_id product_name torf
5 13176 Bag of Organic Bananas ?
5 13176 Bag of Organic Bananas s
5 13176 Bag of Organic Bananas ?
5 13176 Bag of Organic Bananas ?
5 13176 Bag of Organic Bananas s
5 13176 Bag of Organic Bananas ?
5 13176 Bag of Organic Bananas ?
5 13176 Bag of Organic Bananas ?
5 13176 Bag of Organic Bananas ?
5 13176 Bag of Organic Bananas ?
5 13176 Bag of Organic Bananas s
5 13176 Bag of Organic Bananas ?
...
Thanks for any help you can provide.
...
Right now I'm using a view with 500 products and 1000 rows of orders, I'm still thinking what the final size for orders, probably something like 500k to 1mi rows. But the datset I`m using has something like 4.5mi rows.
The expected result would be something like the following.
order_id product_id product_name torf
5 123 tomatoes s
5 124 limes ?
...
100 123 tomatoes ?
100 124 limes ?
mysql
mysql
asked Nov 22 '18 at 2:27
Gustavo AzevedoGustavo Azevedo
33
asked Nov 22 '18 at 2:27
Gustavo AzevedoGustavo Azevedo
33
edited Nov 22 '18 at 2:50
Gustavo Azevedo
asked Nov 22 '18 at 2:27
Gustavo AzevedoGustavo Azevedo
33
asked Nov 22 '18 at 2:27
Gustavo AzevedoGustavo Azevedo
33
asked Nov 22 '18 at 2:27
Gustavo AzevedoGustavo Azevedo
33
33
Can you provide a minimum sample of data and the expected output from it, this will help a lot to understand the goal. Also, add the tables schema.
– Shidersz
Nov 22 '18 at 2:36
Sample data and expected result will be helpful.. And avoid using comma for join.. :)
– dwir182
Nov 22 '18 at 2:37
add a comment |
Can you provide a minimum sample of data and the expected output from it, this will help a lot to understand the goal. Also, add the tables schema.
– Shidersz
Nov 22 '18 at 2:36
Sample data and expected result will be helpful.. And avoid using comma for join.. :)
– dwir182
Nov 22 '18 at 2:37
Can you provide a minimum sample of data and the expected output from it, this will help a lot to understand the goal. Also, add the tables schema.
– Shidersz
Nov 22 '18 at 2:36
Can you provide a minimum sample of data and the expected output from it, this will help a lot to understand the goal. Also, add the tables schema.
– Shidersz
Nov 22 '18 at 2:36
Sample data and expected result will be helpful.. And avoid using comma for join.. :)
– dwir182
Nov 22 '18 at 2:37
Sample data and expected result will be helpful.. And avoid using comma for join.. :)
– dwir182
Nov 22 '18 at 2:37
add a comment |
1 Answer
1
active
oldest
votes
PLEASE don't use commas between table names, and certainly don't do this as a substitute for cross join. I'm not sure why you would want every product against every order, but to do that you do need a full Cartesian product and hence you need a cross join.
SELECT
r1.order_id
, p1.product_id
, p1.product_name
, CASE
WHEN r1.product_id = p1.product_id THEN 's'
ELSE '?'
END AS 'torf'
FROM order_limit1000 r1
CROSS JOIN product_limit500 p1
ORDER BY
r1.order_id
, p1.product_id
, p1.product_name
Given that the order table already has a product_id in it, you do not need another correlated subquery to test if you should output 's' or '?'. nb: I am assuming that product name comes from the product table, not the order table.
order_id R1.product_id P1.product_id product_name torf
---------- --------------- --------------- -------------- ------
5 123 123 tomatoes s << r1.product_id = p1.product_id
5 124 limes ?
...
100 123 tomatoes ?
100 124 limes ?
answered Nov 22 '18 at 2:50
Used_By_AlreadyUsed_By_Already
22.8k21838
Thank you for your help.
– Gustavo Azevedo
Nov 22 '18 at 11:29
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%2f53423063%2fmysql-using-case-when-with-in-subquery%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
PLEASE don't use commas between table names, and certainly don't do this as a substitute for cross join. I'm not sure why you would want every product against every order, but to do that you do need a full Cartesian product and hence you need a cross join.
SELECT
r1.order_id
, p1.product_id
, p1.product_name
, CASE
WHEN r1.product_id = p1.product_id THEN 's'
ELSE '?'
END AS 'torf'
FROM order_limit1000 r1
CROSS JOIN product_limit500 p1
ORDER BY
r1.order_id
, p1.product_id
, p1.product_name
Given that the order table already has a product_id in it, you do not need another correlated subquery to test if you should output 's' or '?'. nb: I am assuming that product name comes from the product table, not the order table.
order_id R1.product_id P1.product_id product_name torf
---------- --------------- --------------- -------------- ------
5 123 123 tomatoes s << r1.product_id = p1.product_id
5 124 limes ?
...
100 123 tomatoes ?
100 124 limes ?
answered Nov 22 '18 at 2:50
Used_By_AlreadyUsed_By_Already
22.8k21838
Thank you for your help.
– Gustavo Azevedo
Nov 22 '18 at 11:29
add a comment |
PLEASE don't use commas between table names, and certainly don't do this as a substitute for cross join. I'm not sure why you would want every product against every order, but to do that you do need a full Cartesian product and hence you need a cross join.
SELECT
r1.order_id
, p1.product_id
, p1.product_name
, CASE
WHEN r1.product_id = p1.product_id THEN 's'
ELSE '?'
END AS 'torf'
FROM order_limit1000 r1
CROSS JOIN product_limit500 p1
ORDER BY
r1.order_id
, p1.product_id
, p1.product_name
Given that the order table already has a product_id in it, you do not need another correlated subquery to test if you should output 's' or '?'. nb: I am assuming that product name comes from the product table, not the order table.
order_id R1.product_id P1.product_id product_name torf
---------- --------------- --------------- -------------- ------
5 123 123 tomatoes s << r1.product_id = p1.product_id
5 124 limes ?
...
100 123 tomatoes ?
100 124 limes ?
answered Nov 22 '18 at 2:50
Used_By_AlreadyUsed_By_Already
22.8k21838
Thank you for your help.
– Gustavo Azevedo
Nov 22 '18 at 11:29
add a comment |
PLEASE don't use commas between table names, and certainly don't do this as a substitute for cross join. I'm not sure why you would want every product against every order, but to do that you do need a full Cartesian product and hence you need a cross join.
SELECT
r1.order_id
, p1.product_id
, p1.product_name
, CASE
WHEN r1.product_id = p1.product_id THEN 's'
ELSE '?'
END AS 'torf'
FROM order_limit1000 r1
CROSS JOIN product_limit500 p1
ORDER BY
r1.order_id
, p1.product_id
, p1.product_name
Given that the order table already has a product_id in it, you do not need another correlated subquery to test if you should output 's' or '?'. nb: I am assuming that product name comes from the product table, not the order table.
order_id R1.product_id P1.product_id product_name torf
---------- --------------- --------------- -------------- ------
5 123 123 tomatoes s << r1.product_id = p1.product_id
5 124 limes ?
...
100 123 tomatoes ?
100 124 limes ?
answered Nov 22 '18 at 2:50
Used_By_AlreadyUsed_By_Already
22.8k21838
PLEASE don't use commas between table names, and certainly don't do this as a substitute for cross join. I'm not sure why you would want every product against every order, but to do that you do need a full Cartesian product and hence you need a cross join.
SELECT
r1.order_id
, p1.product_id
, p1.product_name
, CASE
WHEN r1.product_id = p1.product_id THEN 's'
ELSE '?'
END AS 'torf'
FROM order_limit1000 r1
CROSS JOIN product_limit500 p1
ORDER BY
r1.order_id
, p1.product_id
, p1.product_name
Given that the order table already has a product_id in it, you do not need another correlated subquery to test if you should output 's' or '?'. nb: I am assuming that product name comes from the product table, not the order table.
order_id R1.product_id P1.product_id product_name torf
---------- --------------- --------------- -------------- ------
5 123 123 tomatoes s << r1.product_id = p1.product_id
5 124 limes ?
...
100 123 tomatoes ?
100 124 limes ?
answered Nov 22 '18 at 2:50
Used_By_AlreadyUsed_By_Already
22.8k21838
edited Nov 22 '18 at 2:58
answered Nov 22 '18 at 2:50
Used_By_AlreadyUsed_By_Already
22.8k21838
answered Nov 22 '18 at 2:50
Used_By_AlreadyUsed_By_Already
22.8k21838
answered Nov 22 '18 at 2:50
Used_By_AlreadyUsed_By_Already
22.8k21838
22.8k21838
Thank you for your help.
– Gustavo Azevedo
Nov 22 '18 at 11:29
add a comment |
Thank you for your help.
– Gustavo Azevedo
Nov 22 '18 at 11:29
Thank you for your help.
– Gustavo Azevedo
Nov 22 '18 at 11:29
Thank you for your help.
– Gustavo Azevedo
Nov 22 '18 at 11:29
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%2f53423063%2fmysql-using-case-when-with-in-subquery%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 you provide a minimum sample of data and the expected output from it, this will help a lot to understand the goal. Also, add the tables schema.
– Shidersz
Nov 22 '18 at 2:36
Sample data and expected result will be helpful.. And avoid using comma for join.. :)
– dwir182
Nov 22 '18 at 2:37