Having Trouble When Selecting Duplicates

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
jbh
Forum Commoner
Posts: 89
Joined: Tue Dec 05, 2006 7:01 pm

Having Trouble When Selecting Duplicates

Post by jbh »

Imagine having a table of customers

cid | orderid | status (shipped,declined/approved)
1 | 101 | approved
1 | 102 | approved

I want to be able to show only duplicate orders that were approved

In my db table, if I use this query:

Code: Select all

SELECT * FROM orders GROUP BY (customers_id) HAVING count(customers_id)>1
I get a bunch of customers who have ordered more than 1 time. Of course, the problem is that they have a whole range of
status updates, from shipped, approved/etc. I only want approved. So I tried this:

Code: Select all

SELECT * FROM orders GROUP BY (customers_id) HAVING count(customers_id)>1 AND STATUS='shipped'
It gives me fewer results but there is a flaw. Not all orders for that customer are 'approved'. Some are shipped/pending/etc

I only want records that are multiple orders from the same person while *also* only being 'approved' status. How do I go about
completing the query so only orders that are 'approved' display among the customers that ordered more than 1 product?

Thanks
Last edited by Benjamin on Fri Jun 12, 2009 7:22 am, edited 1 time in total.
Reason: Changed code type from text to sql.
jbh
Forum Commoner
Posts: 89
Joined: Tue Dec 05, 2006 7:01 pm

Re: Having Trouble When Selecting Duplicates

Post by jbh »

As an update, I was able to find the distinct users who have multiple purchases with
status='approved' - it's just that it won't list all of the orders (Only the person and order/customer id)

Code: Select all

 
 
SELECT *
FROM customers WHERE
customers.STATUS = 'approved'
GROUP BY (cid) HAVING count(cid)>1
 
 
While this shows

customer | order ID | etc as one row

I am hoping for

order id 1 - sales info
order id 2 - same person, sales info
Last edited by Benjamin on Fri Jun 12, 2009 9:26 am, edited 1 time in total.
Reason: Changed code type from text to sql.
Post Reply