MYSQL query conditional statement

Question

I have a problem with a query: I have a table with a list of orders placed by customers

orders: customer_id, payment_method ...some other field

I need to extract the customer_id and a 'YES' if the customer has made at least one payment with a specific mode of payment

I tried something like this:

SELECT DISTINCT o.customer_id, 
CASE WHEN o.payment_method = 10 THEN 'YES' ELSE 'NO' END AS credit_card
FROM orders AS o
WHERE o.year = 2012
ORDER BY o.customer_id

but in case the customer has made payments with different payment methods are shown two records, one with 'YES' one with a 'NO'... it's possible to get only one value?

Roland Bouman · Accepted Answer · 2012-07-19 16:22:48Z

2

SELECT   o.customer_id, 
         IF(MAX(IF(o.payment_method=10, 1, 0)) = 1, 'YES', 'NO') credit_card
FROM     orders o
WHERE    o.year = 2012
GROUP BY o.customer_id

answered Jul 19, 2012 at 16:22

Roland Bouman

32.1k8 gold badges69 silver badges69 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

lc. · Accepted Answer · 2012-07-19 16:23:56Z

0

You can do a self join with group by and test for NULL in the joined table (untested):

SELECT customer_id, IF(p.customer_id IS NULL, 'NO', 'YES')
FROM orders o
LEFT OUTER JOIN orders p ON o.customer_id = p.customer_id AND p.payment_method = 10
    AND p.year = 2012
WHERE o.year = 2012
GROUP BY o.customer_id
ORDER BY o.customer_id

Not sure if you need the p.year = 2012 condition or not from the question.

answered Jul 19, 2012 at 16:23

lc.

117k21 gold badges163 silver badges190 bronze badges

Collectives™ on Stack Overflow

MYSQL query conditional statement

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related