Mixing
MySQL select duplicate payment pending orders with no corresponding paid order
My Table structure is:
orders
+------+-------------+----------------+-------------+
| id | customer_id | payment_status | created_on|
+------+-------------+----------------+-------------+
| 1 | 1 | unpaid | 2018-12-28 |
| 2 | 1 | unpaid | 2018-12-29 |
| 3 | 2 | unpaid | 2018-12-29 |
| 4 | 2 | unpaid | 2018-12-29 |
| 5 | 4 | paid | 2018-12-30 |
| 6 | 3 | unpaid | 2018-12-30 |
+------+-------------+----------------+-------------+
order_items
+------+-----------+-------------+----------+-------+
| id | order_id | product_id | quantity | price |
+------+-----------+-------------+----------+-------+
| 1 | 1 | 4 | 2 | 20.50 |
| 2 | 1 | 5 | 2 | 25.00 |
| 3 | 2 | 4 | 2 | 20.50 |
| 4 | 2 | 5 | 2 | 25.00 |
| 5 | 3 | 1 | 1 | 20.00 |
| 6 | 3 | 2 | 1 | 25.00 |
| 7 | 4 | 1 | 1 | 20.00 |
| 8 | 4 | 2 | 1 | 25.00 |
| 9 | 5 | 4 | 2 | 20.50 |
| 10 | 5 | 5 | 2 | 25.00 |
| 11 | 6 | 3 | 4 | 15.00 |
+------+-----------+-------------+----------+-------+
customer
+-----+---------------+----------+
| id | email | name |
+-----+---------------+----------+
| 1 | abc@mail.com | user 1 |
| 2 | xyz@mail.com | user 2 |
| 3 | pqr@mail.com | user 3 |
| 4 | abc@mail.com | user 4 |
+-----+---------------+----------+
Q: I want the data as orders which are under one customer email with pending status and no paid status orders under that customer with in a week
Expected output: 1
single order with no corresponding paid order with in a week
+------+-------------+----------------+-------------+
| id | customer_id | payment_status | created_on|
+------+-------------+----------------+-------------+
| 3 | 2 | unpaid | 2018-12-29 |
| 4 | 2 | unpaid | 2018-12-29 |
| 6 | 3 | unpaid | 2018-12-30 |
+------+-------------+----------------+-------------+
Q: I want the data as if there are 2 orders which has same products and same quantity under one customer email with pending status and no paid status orders under that customer with in a week
Expected output: 2
two orders with no corresponding paid order with in a week
+------+-------------+----------------+-------------+
| id | customer_id | payment_status | created_on|
+------+-------------+----------------+-------------+
| 3 | 2 | unpaid | 2018-12-29 |
| 4 | 2 | unpaid | 2018-12-29 |
+------+-------------+----------------+-------------+
Thanks in advance
mysql mysqli
asked Jan 2 at 14:00
NitheeNithee
12211
add a comment |
My Table structure is:
orders
+------+-------------+----------------+-------------+
| id | customer_id | payment_status | created_on|
+------+-------------+----------------+-------------+
| 1 | 1 | unpaid | 2018-12-28 |
| 2 | 1 | unpaid | 2018-12-29 |
| 3 | 2 | unpaid | 2018-12-29 |
| 4 | 2 | unpaid | 2018-12-29 |
| 5 | 4 | paid | 2018-12-30 |
| 6 | 3 | unpaid | 2018-12-30 |
+------+-------------+----------------+-------------+
order_items
+------+-----------+-------------+----------+-------+
| id | order_id | product_id | quantity | price |
+------+-----------+-------------+----------+-------+
| 1 | 1 | 4 | 2 | 20.50 |
| 2 | 1 | 5 | 2 | 25.00 |
| 3 | 2 | 4 | 2 | 20.50 |
| 4 | 2 | 5 | 2 | 25.00 |
| 5 | 3 | 1 | 1 | 20.00 |
| 6 | 3 | 2 | 1 | 25.00 |
| 7 | 4 | 1 | 1 | 20.00 |
| 8 | 4 | 2 | 1 | 25.00 |
| 9 | 5 | 4 | 2 | 20.50 |
| 10 | 5 | 5 | 2 | 25.00 |
| 11 | 6 | 3 | 4 | 15.00 |
+------+-----------+-------------+----------+-------+
customer
+-----+---------------+----------+
| id | email | name |
+-----+---------------+----------+
| 1 | abc@mail.com | user 1 |
| 2 | xyz@mail.com | user 2 |
| 3 | pqr@mail.com | user 3 |
| 4 | abc@mail.com | user 4 |
+-----+---------------+----------+
Q: I want the data as orders which are under one customer email with pending status and no paid status orders under that customer with in a week
Expected output: 1
single order with no corresponding paid order with in a week
+------+-------------+----------------+-------------+
| id | customer_id | payment_status | created_on|
+------+-------------+----------------+-------------+
| 3 | 2 | unpaid | 2018-12-29 |
| 4 | 2 | unpaid | 2018-12-29 |
| 6 | 3 | unpaid | 2018-12-30 |
+------+-------------+----------------+-------------+
Q: I want the data as if there are 2 orders which has same products and same quantity under one customer email with pending status and no paid status orders under that customer with in a week
Expected output: 2
two orders with no corresponding paid order with in a week
+------+-------------+----------------+-------------+
| id | customer_id | payment_status | created_on|
+------+-------------+----------------+-------------+
| 3 | 2 | unpaid | 2018-12-29 |
| 4 | 2 | unpaid | 2018-12-29 |
+------+-------------+----------------+-------------+
Thanks in advance
mysql mysqli
asked Jan 2 at 14:00
NitheeNithee
12211
Please post your actual SQL as text in your question.
– Thomas G
Jan 2 at 14:15
@ThomasG, I have updated the question, Please check
– Nithee
Jan 3 at 8:38
"with in a week" -- "within a week of ordering"? or "since a week ago"?
– Rick James
Jan 6 at 16:16
add a comment |
My Table structure is:
orders
+------+-------------+----------------+-------------+
| id | customer_id | payment_status | created_on|
+------+-------------+----------------+-------------+
| 1 | 1 | unpaid | 2018-12-28 |
| 2 | 1 | unpaid | 2018-12-29 |
| 3 | 2 | unpaid | 2018-12-29 |
| 4 | 2 | unpaid | 2018-12-29 |
| 5 | 4 | paid | 2018-12-30 |
| 6 | 3 | unpaid | 2018-12-30 |
+------+-------------+----------------+-------------+
order_items
+------+-----------+-------------+----------+-------+
| id | order_id | product_id | quantity | price |
+------+-----------+-------------+----------+-------+
| 1 | 1 | 4 | 2 | 20.50 |
| 2 | 1 | 5 | 2 | 25.00 |
| 3 | 2 | 4 | 2 | 20.50 |
| 4 | 2 | 5 | 2 | 25.00 |
| 5 | 3 | 1 | 1 | 20.00 |
| 6 | 3 | 2 | 1 | 25.00 |
| 7 | 4 | 1 | 1 | 20.00 |
| 8 | 4 | 2 | 1 | 25.00 |
| 9 | 5 | 4 | 2 | 20.50 |
| 10 | 5 | 5 | 2 | 25.00 |
| 11 | 6 | 3 | 4 | 15.00 |
+------+-----------+-------------+----------+-------+
customer
+-----+---------------+----------+
| id | email | name |
+-----+---------------+----------+
| 1 | abc@mail.com | user 1 |
| 2 | xyz@mail.com | user 2 |
| 3 | pqr@mail.com | user 3 |
| 4 | abc@mail.com | user 4 |
+-----+---------------+----------+
Q: I want the data as orders which are under one customer email with pending status and no paid status orders under that customer with in a week
Expected output: 1
single order with no corresponding paid order with in a week
+------+-------------+----------------+-------------+
| id | customer_id | payment_status | created_on|
+------+-------------+----------------+-------------+
| 3 | 2 | unpaid | 2018-12-29 |
| 4 | 2 | unpaid | 2018-12-29 |
| 6 | 3 | unpaid | 2018-12-30 |
+------+-------------+----------------+-------------+
Q: I want the data as if there are 2 orders which has same products and same quantity under one customer email with pending status and no paid status orders under that customer with in a week
Expected output: 2
two orders with no corresponding paid order with in a week
+------+-------------+----------------+-------------+
| id | customer_id | payment_status | created_on|
+------+-------------+----------------+-------------+
| 3 | 2 | unpaid | 2018-12-29 |
| 4 | 2 | unpaid | 2018-12-29 |
+------+-------------+----------------+-------------+
Thanks in advance
mysql mysqli
asked Jan 2 at 14:00
NitheeNithee
12211
My Table structure is:
orders
+------+-------------+----------------+-------------+
| id | customer_id | payment_status | created_on|
+------+-------------+----------------+-------------+
| 1 | 1 | unpaid | 2018-12-28 |
| 2 | 1 | unpaid | 2018-12-29 |
| 3 | 2 | unpaid | 2018-12-29 |
| 4 | 2 | unpaid | 2018-12-29 |
| 5 | 4 | paid | 2018-12-30 |
| 6 | 3 | unpaid | 2018-12-30 |
+------+-------------+----------------+-------------+
order_items
+------+-----------+-------------+----------+-------+
| id | order_id | product_id | quantity | price |
+------+-----------+-------------+----------+-------+
| 1 | 1 | 4 | 2 | 20.50 |
| 2 | 1 | 5 | 2 | 25.00 |
| 3 | 2 | 4 | 2 | 20.50 |
| 4 | 2 | 5 | 2 | 25.00 |
| 5 | 3 | 1 | 1 | 20.00 |
| 6 | 3 | 2 | 1 | 25.00 |
| 7 | 4 | 1 | 1 | 20.00 |
| 8 | 4 | 2 | 1 | 25.00 |
| 9 | 5 | 4 | 2 | 20.50 |
| 10 | 5 | 5 | 2 | 25.00 |
| 11 | 6 | 3 | 4 | 15.00 |
+------+-----------+-------------+----------+-------+
customer
+-----+---------------+----------+
| id | email | name |
+-----+---------------+----------+
| 1 | abc@mail.com | user 1 |
| 2 | xyz@mail.com | user 2 |
| 3 | pqr@mail.com | user 3 |
| 4 | abc@mail.com | user 4 |
+-----+---------------+----------+
Q: I want the data as orders which are under one customer email with pending status and no paid status orders under that customer with in a week
Expected output: 1
single order with no corresponding paid order with in a week
+------+-------------+----------------+-------------+
| id | customer_id | payment_status | created_on|
+------+-------------+----------------+-------------+
| 3 | 2 | unpaid | 2018-12-29 |
| 4 | 2 | unpaid | 2018-12-29 |
| 6 | 3 | unpaid | 2018-12-30 |
+------+-------------+----------------+-------------+
Q: I want the data as if there are 2 orders which has same products and same quantity under one customer email with pending status and no paid status orders under that customer with in a week
Expected output: 2
two orders with no corresponding paid order with in a week
+------+-------------+----------------+-------------+
| id | customer_id | payment_status | created_on|
+------+-------------+----------------+-------------+
| 3 | 2 | unpaid | 2018-12-29 |
| 4 | 2 | unpaid | 2018-12-29 |
+------+-------------+----------------+-------------+
Thanks in advance
mysql mysqli
mysql mysqli
asked Jan 2 at 14:00
NitheeNithee
12211
asked Jan 2 at 14:00
NitheeNithee
12211
edited Jan 7 at 5:52
Nithee
asked Jan 2 at 14:00
NitheeNithee
12211
asked Jan 2 at 14:00
NitheeNithee
12211
asked Jan 2 at 14:00
NitheeNithee
12211
12211
Please post your actual SQL as text in your question.
– Thomas G
Jan 2 at 14:15
@ThomasG, I have updated the question, Please check
– Nithee
Jan 3 at 8:38
"with in a week" -- "within a week of ordering"? or "since a week ago"?
– Rick James
Jan 6 at 16:16
add a comment |
Please post your actual SQL as text in your question.
– Thomas G
Jan 2 at 14:15
@ThomasG, I have updated the question, Please check
– Nithee
Jan 3 at 8:38
"with in a week" -- "within a week of ordering"? or "since a week ago"?
– Rick James
Jan 6 at 16:16
Please post your actual SQL as text in your question.
– Thomas G
Jan 2 at 14:15
Please post your actual SQL as text in your question.
– Thomas G
Jan 2 at 14:15
@ThomasG, I have updated the question, Please check
– Nithee
Jan 3 at 8:38
@ThomasG, I have updated the question, Please check
– Nithee
Jan 3 at 8:38
"with in a week" -- "within a week of ordering"? or "since a week ago"?
– Rick James
Jan 6 at 16:16
"with in a week" -- "within a week of ordering"? or "since a week ago"?
– Rick James
Jan 6 at 16:16
add a comment |
2 Answers
2
active
oldest
votes
The first query is dubious -- Did you really mean email or customer_id? The latter should be how you designed the schema to distinguish one "customer" from another. Think that through. (And fix the data to make it clear.) Meanwhile, I will assume customer_id distinguishes customers.
I can't wrap my head around the purpose of the first query. You are looking for customers that paid for a later Order, but have not paid for an earlier order? Or looking for mis-postings in the database? Anyway, here is a shot at it:
SELECT Unpd.id, Unpd.customer_id, Unpd.payment_status, Unpd.created_on
FROM Orders AS Pd ON Pd.customer_id = C.id
AND payment_status = 'paid'
WHERE NOT EXISTS
(
SELECT 1
FROM Orders AS Pd
WHERE Pd.customer_id = C.id
AND Pd.payment_status = 'paid'
AND Pd.created_on > NOW() - INTERVAL 1 WEEK
)
Second query. I rephrase it as: Locate two (or more) orders (paid or unpaid) by the same customer ON the same day (but not checking that the items are the same):
SELECT O2.id, O2.customer_id, O2.payment_status, O2.created_on
FROM
(
SELECT O.customer_id, O.created_on
FROM Orders AS O
GROUP BY O.customer_id, O.created_on
HAVING COUNT(*) >= 2
) AS MultipleInOneDay
JOIN Orders AS O2 USING (customer_id, created_on)
answered Jan 6 at 16:41
Rick JamesRick James
70.3k563104
Did you really mean email or customer_id? = > yes email ; each time a new order get placed new customer also created even if the customer is present in the database( i agree db need normalization but, I am convening them, but they are not ready to take changes :-( currently need to built 2 filters 1) same email same products same quantity with in last week (fund unpaid orders under an email if no paid order under that email in the given time with same products and quantity) kind of duplicate order
– Nithee
Jan 7 at 5:59
2
Putting off the schema cleanup is more costly in the long run.
– Rick James
Jan 7 at 19:10
add a comment |
I agree completely with Rick about cleaning up the schema
If I've read you correctly, currently your customer table effectively just adds the columns email and name to your orders table
Q1
Assuming you want within a week of today's date and ID fields cannot be null
SELECT ou.*
FROM orders ou /** orders unpaid */
JOIN customer cu /** customer unpaid */
ON cu.id = ou.customer_id
WHERE ou.payment_status = 'unpaid'
AND NOT EXISTS (
SELECT 1
FROM orders op /** orders paid */
JOIN customer cp /** customer paid */
ON cp.id = op.customer_id
WHERE op.payment_status = 'paid'
AND op.created_on > CURDATE() - INTERVAL 1 WEEK /** or >= if required */
AND cp.email = cu.email
)
N.B. As it is more than a week since the paid orders in your examples, you will have to adjust the temporal condition to see the same results
Q2
Same assumptions as Q1, plus assumption that a product_id can only appear once per order
SELECT ou.*
FROM orders ou /** orders unpaid */
JOIN customer cu /** customer unpaid */
ON cu.id = ou.customer_id
JOIN (
SELECT GROUP_CONCAT(oudc.id) orders_csv
FROM (
SELECT oui.id,
cui.email,
GROUP_CONCAT(oiui.product_id ORDER BY oiui.product_id) products,
GROUP_CONCAT(oiui.quantity ORDER BY oiui.product_id) quantity
FROM orders oui /** orders unpaid internal */
JOIN customer cui /** customer unpaid internal */
ON cui.id = oui.customer_id
JOIN order_items oiui /** order items unpaid internal */
ON oiui.order_id = oui.id
WHERE oui.payment_status = 'unpaid'
GROUP BY oui.id,
cui.email
) oudc /** orders unpaid dupe check */
GROUP BY oudc.email,
oudc.products,
oudc.quantity
HAVING COUNT(*) = 2 /** or >=2 if required */
) oud /** orders unpaid dupes */
ON FIND_IN_SET(ou.id, oud.orders_csv) > 0
WHERE ou.payment_status = 'unpaid'
AND NOT EXISTS (
SELECT 1
FROM orders op /** orders paid */
JOIN customer cp /** customer paid */
ON cp.id = op.customer_id
WHERE op.payment_status = 'paid'
AND op.created_on > CURDATE() - INTERVAL 1 WEEK /** or >= if required */
AND cp.email = cu.email
)
N.B. As it is more than a week since the paid orders in your examples, you will have to adjust the temporal condition to see the same results
This query is only roughly tested and is probably woefully slow. I suggest you run each nested select query individually (starting at the deepest) to see what is happening. Basically it concatenates the orders into 1 row each, then concatenates duplicate orders with the same email into 1 row each, then checks for orders in this row using a similar logic to Q1
If you can have the same product_id more than once per order, you can normalize with a further nested grouping select within my orders unpaid dupe check subquery
SQLfiddle
I have also created an SQLfiddle to demonstrate these 2 queries in action on your example data. I have, however, adjusted the dates of the example orders so that they depend on the current date
answered Jan 8 at 19:40
ArthArth
8,98342348
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%2f54007670%2fmysql-select-duplicate-payment-pending-orders-with-no-corresponding-paid-order%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
The first query is dubious -- Did you really mean email or customer_id? The latter should be how you designed the schema to distinguish one "customer" from another. Think that through. (And fix the data to make it clear.) Meanwhile, I will assume customer_id distinguishes customers.
I can't wrap my head around the purpose of the first query. You are looking for customers that paid for a later Order, but have not paid for an earlier order? Or looking for mis-postings in the database? Anyway, here is a shot at it:
SELECT Unpd.id, Unpd.customer_id, Unpd.payment_status, Unpd.created_on
FROM Orders AS Pd ON Pd.customer_id = C.id
AND payment_status = 'paid'
WHERE NOT EXISTS
(
SELECT 1
FROM Orders AS Pd
WHERE Pd.customer_id = C.id
AND Pd.payment_status = 'paid'
AND Pd.created_on > NOW() - INTERVAL 1 WEEK
)
Second query. I rephrase it as: Locate two (or more) orders (paid or unpaid) by the same customer ON the same day (but not checking that the items are the same):
SELECT O2.id, O2.customer_id, O2.payment_status, O2.created_on
FROM
(
SELECT O.customer_id, O.created_on
FROM Orders AS O
GROUP BY O.customer_id, O.created_on
HAVING COUNT(*) >= 2
) AS MultipleInOneDay
JOIN Orders AS O2 USING (customer_id, created_on)
answered Jan 6 at 16:41
Rick JamesRick James
70.3k563104
Did you really mean email or customer_id? = > yes email ; each time a new order get placed new customer also created even if the customer is present in the database( i agree db need normalization but, I am convening them, but they are not ready to take changes :-( currently need to built 2 filters 1) same email same products same quantity with in last week (fund unpaid orders under an email if no paid order under that email in the given time with same products and quantity) kind of duplicate order
– Nithee
Jan 7 at 5:59
2
Putting off the schema cleanup is more costly in the long run.
– Rick James
Jan 7 at 19:10
add a comment |
The first query is dubious -- Did you really mean email or customer_id? The latter should be how you designed the schema to distinguish one "customer" from another. Think that through. (And fix the data to make it clear.) Meanwhile, I will assume customer_id distinguishes customers.
I can't wrap my head around the purpose of the first query. You are looking for customers that paid for a later Order, but have not paid for an earlier order? Or looking for mis-postings in the database? Anyway, here is a shot at it:
SELECT Unpd.id, Unpd.customer_id, Unpd.payment_status, Unpd.created_on
FROM Orders AS Pd ON Pd.customer_id = C.id
AND payment_status = 'paid'
WHERE NOT EXISTS
(
SELECT 1
FROM Orders AS Pd
WHERE Pd.customer_id = C.id
AND Pd.payment_status = 'paid'
AND Pd.created_on > NOW() - INTERVAL 1 WEEK
)
Second query. I rephrase it as: Locate two (or more) orders (paid or unpaid) by the same customer ON the same day (but not checking that the items are the same):
SELECT O2.id, O2.customer_id, O2.payment_status, O2.created_on
FROM
(
SELECT O.customer_id, O.created_on
FROM Orders AS O
GROUP BY O.customer_id, O.created_on
HAVING COUNT(*) >= 2
) AS MultipleInOneDay
JOIN Orders AS O2 USING (customer_id, created_on)
answered Jan 6 at 16:41
Rick JamesRick James
70.3k563104
Did you really mean email or customer_id? = > yes email ; each time a new order get placed new customer also created even if the customer is present in the database( i agree db need normalization but, I am convening them, but they are not ready to take changes :-( currently need to built 2 filters 1) same email same products same quantity with in last week (fund unpaid orders under an email if no paid order under that email in the given time with same products and quantity) kind of duplicate order
– Nithee
Jan 7 at 5:59
2
Putting off the schema cleanup is more costly in the long run.
– Rick James
Jan 7 at 19:10
add a comment |
The first query is dubious -- Did you really mean email or customer_id? The latter should be how you designed the schema to distinguish one "customer" from another. Think that through. (And fix the data to make it clear.) Meanwhile, I will assume customer_id distinguishes customers.
I can't wrap my head around the purpose of the first query. You are looking for customers that paid for a later Order, but have not paid for an earlier order? Or looking for mis-postings in the database? Anyway, here is a shot at it:
SELECT Unpd.id, Unpd.customer_id, Unpd.payment_status, Unpd.created_on
FROM Orders AS Pd ON Pd.customer_id = C.id
AND payment_status = 'paid'
WHERE NOT EXISTS
(
SELECT 1
FROM Orders AS Pd
WHERE Pd.customer_id = C.id
AND Pd.payment_status = 'paid'
AND Pd.created_on > NOW() - INTERVAL 1 WEEK
)
Second query. I rephrase it as: Locate two (or more) orders (paid or unpaid) by the same customer ON the same day (but not checking that the items are the same):
SELECT O2.id, O2.customer_id, O2.payment_status, O2.created_on
FROM
(
SELECT O.customer_id, O.created_on
FROM Orders AS O
GROUP BY O.customer_id, O.created_on
HAVING COUNT(*) >= 2
) AS MultipleInOneDay
JOIN Orders AS O2 USING (customer_id, created_on)
answered Jan 6 at 16:41
Rick JamesRick James
70.3k563104
The first query is dubious -- Did you really mean email or customer_id? The latter should be how you designed the schema to distinguish one "customer" from another. Think that through. (And fix the data to make it clear.) Meanwhile, I will assume customer_id distinguishes customers.
I can't wrap my head around the purpose of the first query. You are looking for customers that paid for a later Order, but have not paid for an earlier order? Or looking for mis-postings in the database? Anyway, here is a shot at it:
SELECT Unpd.id, Unpd.customer_id, Unpd.payment_status, Unpd.created_on
FROM Orders AS Pd ON Pd.customer_id = C.id
AND payment_status = 'paid'
WHERE NOT EXISTS
(
SELECT 1
FROM Orders AS Pd
WHERE Pd.customer_id = C.id
AND Pd.payment_status = 'paid'
AND Pd.created_on > NOW() - INTERVAL 1 WEEK
)
Second query. I rephrase it as: Locate two (or more) orders (paid or unpaid) by the same customer ON the same day (but not checking that the items are the same):
SELECT O2.id, O2.customer_id, O2.payment_status, O2.created_on
FROM
(
SELECT O.customer_id, O.created_on
FROM Orders AS O
GROUP BY O.customer_id, O.created_on
HAVING COUNT(*) >= 2
) AS MultipleInOneDay
JOIN Orders AS O2 USING (customer_id, created_on)
answered Jan 6 at 16:41
Rick JamesRick James
70.3k563104
answered Jan 6 at 16:41
Rick JamesRick James
70.3k563104
answered Jan 6 at 16:41
Rick JamesRick James
70.3k563104
answered Jan 6 at 16:41
Rick JamesRick James
70.3k563104
70.3k563104
Did you really mean email or customer_id? = > yes email ; each time a new order get placed new customer also created even if the customer is present in the database( i agree db need normalization but, I am convening them, but they are not ready to take changes :-( currently need to built 2 filters 1) same email same products same quantity with in last week (fund unpaid orders under an email if no paid order under that email in the given time with same products and quantity) kind of duplicate order
– Nithee
Jan 7 at 5:59
2
Putting off the schema cleanup is more costly in the long run.
– Rick James
Jan 7 at 19:10
add a comment |
Did you really mean email or customer_id? = > yes email ; each time a new order get placed new customer also created even if the customer is present in the database( i agree db need normalization but, I am convening them, but they are not ready to take changes :-( currently need to built 2 filters 1) same email same products same quantity with in last week (fund unpaid orders under an email if no paid order under that email in the given time with same products and quantity) kind of duplicate order
– Nithee
Jan 7 at 5:59
2
Putting off the schema cleanup is more costly in the long run.
– Rick James
Jan 7 at 19:10
Did you really mean email or customer_id? = > yes email ; each time a new order get placed new customer also created even if the customer is present in the database( i agree db need normalization but, I am convening them, but they are not ready to take changes :-( currently need to built 2 filters 1) same email same products same quantity with in last week (fund unpaid orders under an email if no paid order under that email in the given time with same products and quantity) kind of duplicate order
– Nithee
Jan 7 at 5:59
Did you really mean email or customer_id? = > yes email ; each time a new order get placed new customer also created even if the customer is present in the database( i agree db need normalization but, I am convening them, but they are not ready to take changes :-( currently need to built 2 filters 1) same email same products same quantity with in last week (fund unpaid orders under an email if no paid order under that email in the given time with same products and quantity) kind of duplicate order
– Nithee
Jan 7 at 5:59
2
2
Putting off the schema cleanup is more costly in the long run.
– Rick James
Jan 7 at 19:10
Putting off the schema cleanup is more costly in the long run.
– Rick James
Jan 7 at 19:10
add a comment |
I agree completely with Rick about cleaning up the schema
If I've read you correctly, currently your customer table effectively just adds the columns email and name to your orders table
Q1
Assuming you want within a week of today's date and ID fields cannot be null
SELECT ou.*
FROM orders ou /** orders unpaid */
JOIN customer cu /** customer unpaid */
ON cu.id = ou.customer_id
WHERE ou.payment_status = 'unpaid'
AND NOT EXISTS (
SELECT 1
FROM orders op /** orders paid */
JOIN customer cp /** customer paid */
ON cp.id = op.customer_id
WHERE op.payment_status = 'paid'
AND op.created_on > CURDATE() - INTERVAL 1 WEEK /** or >= if required */
AND cp.email = cu.email
)
N.B. As it is more than a week since the paid orders in your examples, you will have to adjust the temporal condition to see the same results
Q2
Same assumptions as Q1, plus assumption that a product_id can only appear once per order
SELECT ou.*
FROM orders ou /** orders unpaid */
JOIN customer cu /** customer unpaid */
ON cu.id = ou.customer_id
JOIN (
SELECT GROUP_CONCAT(oudc.id) orders_csv
FROM (
SELECT oui.id,
cui.email,
GROUP_CONCAT(oiui.product_id ORDER BY oiui.product_id) products,
GROUP_CONCAT(oiui.quantity ORDER BY oiui.product_id) quantity
FROM orders oui /** orders unpaid internal */
JOIN customer cui /** customer unpaid internal */
ON cui.id = oui.customer_id
JOIN order_items oiui /** order items unpaid internal */
ON oiui.order_id = oui.id
WHERE oui.payment_status = 'unpaid'
GROUP BY oui.id,
cui.email
) oudc /** orders unpaid dupe check */
GROUP BY oudc.email,
oudc.products,
oudc.quantity
HAVING COUNT(*) = 2 /** or >=2 if required */
) oud /** orders unpaid dupes */
ON FIND_IN_SET(ou.id, oud.orders_csv) > 0
WHERE ou.payment_status = 'unpaid'
AND NOT EXISTS (
SELECT 1
FROM orders op /** orders paid */
JOIN customer cp /** customer paid */
ON cp.id = op.customer_id
WHERE op.payment_status = 'paid'
AND op.created_on > CURDATE() - INTERVAL 1 WEEK /** or >= if required */
AND cp.email = cu.email
)
N.B. As it is more than a week since the paid orders in your examples, you will have to adjust the temporal condition to see the same results
This query is only roughly tested and is probably woefully slow. I suggest you run each nested select query individually (starting at the deepest) to see what is happening. Basically it concatenates the orders into 1 row each, then concatenates duplicate orders with the same email into 1 row each, then checks for orders in this row using a similar logic to Q1
If you can have the same product_id more than once per order, you can normalize with a further nested grouping select within my orders unpaid dupe check subquery
SQLfiddle
I have also created an SQLfiddle to demonstrate these 2 queries in action on your example data. I have, however, adjusted the dates of the example orders so that they depend on the current date
answered Jan 8 at 19:40
ArthArth
8,98342348
add a comment |
I agree completely with Rick about cleaning up the schema
If I've read you correctly, currently your customer table effectively just adds the columns email and name to your orders table
Q1
Assuming you want within a week of today's date and ID fields cannot be null
SELECT ou.*
FROM orders ou /** orders unpaid */
JOIN customer cu /** customer unpaid */
ON cu.id = ou.customer_id
WHERE ou.payment_status = 'unpaid'
AND NOT EXISTS (
SELECT 1
FROM orders op /** orders paid */
JOIN customer cp /** customer paid */
ON cp.id = op.customer_id
WHERE op.payment_status = 'paid'
AND op.created_on > CURDATE() - INTERVAL 1 WEEK /** or >= if required */
AND cp.email = cu.email
)
N.B. As it is more than a week since the paid orders in your examples, you will have to adjust the temporal condition to see the same results
Q2
Same assumptions as Q1, plus assumption that a product_id can only appear once per order
SELECT ou.*
FROM orders ou /** orders unpaid */
JOIN customer cu /** customer unpaid */
ON cu.id = ou.customer_id
JOIN (
SELECT GROUP_CONCAT(oudc.id) orders_csv
FROM (
SELECT oui.id,
cui.email,
GROUP_CONCAT(oiui.product_id ORDER BY oiui.product_id) products,
GROUP_CONCAT(oiui.quantity ORDER BY oiui.product_id) quantity
FROM orders oui /** orders unpaid internal */
JOIN customer cui /** customer unpaid internal */
ON cui.id = oui.customer_id
JOIN order_items oiui /** order items unpaid internal */
ON oiui.order_id = oui.id
WHERE oui.payment_status = 'unpaid'
GROUP BY oui.id,
cui.email
) oudc /** orders unpaid dupe check */
GROUP BY oudc.email,
oudc.products,
oudc.quantity
HAVING COUNT(*) = 2 /** or >=2 if required */
) oud /** orders unpaid dupes */
ON FIND_IN_SET(ou.id, oud.orders_csv) > 0
WHERE ou.payment_status = 'unpaid'
AND NOT EXISTS (
SELECT 1
FROM orders op /** orders paid */
JOIN customer cp /** customer paid */
ON cp.id = op.customer_id
WHERE op.payment_status = 'paid'
AND op.created_on > CURDATE() - INTERVAL 1 WEEK /** or >= if required */
AND cp.email = cu.email
)
N.B. As it is more than a week since the paid orders in your examples, you will have to adjust the temporal condition to see the same results
This query is only roughly tested and is probably woefully slow. I suggest you run each nested select query individually (starting at the deepest) to see what is happening. Basically it concatenates the orders into 1 row each, then concatenates duplicate orders with the same email into 1 row each, then checks for orders in this row using a similar logic to Q1
If you can have the same product_id more than once per order, you can normalize with a further nested grouping select within my orders unpaid dupe check subquery
SQLfiddle
I have also created an SQLfiddle to demonstrate these 2 queries in action on your example data. I have, however, adjusted the dates of the example orders so that they depend on the current date
answered Jan 8 at 19:40
ArthArth
8,98342348
add a comment |
I agree completely with Rick about cleaning up the schema
If I've read you correctly, currently your customer table effectively just adds the columns email and name to your orders table
Q1
Assuming you want within a week of today's date and ID fields cannot be null
SELECT ou.*
FROM orders ou /** orders unpaid */
JOIN customer cu /** customer unpaid */
ON cu.id = ou.customer_id
WHERE ou.payment_status = 'unpaid'
AND NOT EXISTS (
SELECT 1
FROM orders op /** orders paid */
JOIN customer cp /** customer paid */
ON cp.id = op.customer_id
WHERE op.payment_status = 'paid'
AND op.created_on > CURDATE() - INTERVAL 1 WEEK /** or >= if required */
AND cp.email = cu.email
)
N.B. As it is more than a week since the paid orders in your examples, you will have to adjust the temporal condition to see the same results
Q2
Same assumptions as Q1, plus assumption that a product_id can only appear once per order
SELECT ou.*
FROM orders ou /** orders unpaid */
JOIN customer cu /** customer unpaid */
ON cu.id = ou.customer_id
JOIN (
SELECT GROUP_CONCAT(oudc.id) orders_csv
FROM (
SELECT oui.id,
cui.email,
GROUP_CONCAT(oiui.product_id ORDER BY oiui.product_id) products,
GROUP_CONCAT(oiui.quantity ORDER BY oiui.product_id) quantity
FROM orders oui /** orders unpaid internal */
JOIN customer cui /** customer unpaid internal */
ON cui.id = oui.customer_id
JOIN order_items oiui /** order items unpaid internal */
ON oiui.order_id = oui.id
WHERE oui.payment_status = 'unpaid'
GROUP BY oui.id,
cui.email
) oudc /** orders unpaid dupe check */
GROUP BY oudc.email,
oudc.products,
oudc.quantity
HAVING COUNT(*) = 2 /** or >=2 if required */
) oud /** orders unpaid dupes */
ON FIND_IN_SET(ou.id, oud.orders_csv) > 0
WHERE ou.payment_status = 'unpaid'
AND NOT EXISTS (
SELECT 1
FROM orders op /** orders paid */
JOIN customer cp /** customer paid */
ON cp.id = op.customer_id
WHERE op.payment_status = 'paid'
AND op.created_on > CURDATE() - INTERVAL 1 WEEK /** or >= if required */
AND cp.email = cu.email
)
N.B. As it is more than a week since the paid orders in your examples, you will have to adjust the temporal condition to see the same results
This query is only roughly tested and is probably woefully slow. I suggest you run each nested select query individually (starting at the deepest) to see what is happening. Basically it concatenates the orders into 1 row each, then concatenates duplicate orders with the same email into 1 row each, then checks for orders in this row using a similar logic to Q1
If you can have the same product_id more than once per order, you can normalize with a further nested grouping select within my orders unpaid dupe check subquery
SQLfiddle
I have also created an SQLfiddle to demonstrate these 2 queries in action on your example data. I have, however, adjusted the dates of the example orders so that they depend on the current date
answered Jan 8 at 19:40
ArthArth
8,98342348
I agree completely with Rick about cleaning up the schema
If I've read you correctly, currently your customer table effectively just adds the columns email and name to your orders table
Q1
Assuming you want within a week of today's date and ID fields cannot be null
SELECT ou.*
FROM orders ou /** orders unpaid */
JOIN customer cu /** customer unpaid */
ON cu.id = ou.customer_id
WHERE ou.payment_status = 'unpaid'
AND NOT EXISTS (
SELECT 1
FROM orders op /** orders paid */
JOIN customer cp /** customer paid */
ON cp.id = op.customer_id
WHERE op.payment_status = 'paid'
AND op.created_on > CURDATE() - INTERVAL 1 WEEK /** or >= if required */
AND cp.email = cu.email
)
N.B. As it is more than a week since the paid orders in your examples, you will have to adjust the temporal condition to see the same results
Q2
Same assumptions as Q1, plus assumption that a product_id can only appear once per order
SELECT ou.*
FROM orders ou /** orders unpaid */
JOIN customer cu /** customer unpaid */
ON cu.id = ou.customer_id
JOIN (
SELECT GROUP_CONCAT(oudc.id) orders_csv
FROM (
SELECT oui.id,
cui.email,
GROUP_CONCAT(oiui.product_id ORDER BY oiui.product_id) products,
GROUP_CONCAT(oiui.quantity ORDER BY oiui.product_id) quantity
FROM orders oui /** orders unpaid internal */
JOIN customer cui /** customer unpaid internal */
ON cui.id = oui.customer_id
JOIN order_items oiui /** order items unpaid internal */
ON oiui.order_id = oui.id
WHERE oui.payment_status = 'unpaid'
GROUP BY oui.id,
cui.email
) oudc /** orders unpaid dupe check */
GROUP BY oudc.email,
oudc.products,
oudc.quantity
HAVING COUNT(*) = 2 /** or >=2 if required */
) oud /** orders unpaid dupes */
ON FIND_IN_SET(ou.id, oud.orders_csv) > 0
WHERE ou.payment_status = 'unpaid'
AND NOT EXISTS (
SELECT 1
FROM orders op /** orders paid */
JOIN customer cp /** customer paid */
ON cp.id = op.customer_id
WHERE op.payment_status = 'paid'
AND op.created_on > CURDATE() - INTERVAL 1 WEEK /** or >= if required */
AND cp.email = cu.email
)
N.B. As it is more than a week since the paid orders in your examples, you will have to adjust the temporal condition to see the same results
This query is only roughly tested and is probably woefully slow. I suggest you run each nested select query individually (starting at the deepest) to see what is happening. Basically it concatenates the orders into 1 row each, then concatenates duplicate orders with the same email into 1 row each, then checks for orders in this row using a similar logic to Q1
If you can have the same product_id more than once per order, you can normalize with a further nested grouping select within my orders unpaid dupe check subquery
SQLfiddle
I have also created an SQLfiddle to demonstrate these 2 queries in action on your example data. I have, however, adjusted the dates of the example orders so that they depend on the current date
answered Jan 8 at 19:40
ArthArth
8,98342348
answered Jan 8 at 19:40
ArthArth
8,98342348
answered Jan 8 at 19:40
ArthArth
8,98342348
answered Jan 8 at 19:40
ArthArth
8,98342348
8,98342348
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%2f54007670%2fmysql-select-duplicate-payment-pending-orders-with-no-corresponding-paid-order%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
Please post your actual SQL as text in your question.
– Thomas G
Jan 2 at 14:15
@ThomasG, I have updated the question, Please check
– Nithee
Jan 3 at 8:38
"with in a week" -- "within a week of ordering"? or "since a week ago"?
– Rick James
Jan 6 at 16:16