MySQL – Query to Get Repeated Customers List

MySQLselect

I have a two tables oc_order and oc_customer. I want to get customers who order more than once.

This is sample:

oc_order:

order_id | cus_id | cus_name
-------------------------------
1        | 1      | Kumar
2        | 2      | Vimal
3        | 1      | Kumar
4        | 3      | Ramesh

oc_customer

  cus_id   | cus_name |  cus_address
    -----------------------------------
    1        | Kumar    |  Chennai
    2        | Vimal    |  Coimbatore
    3        | Ramesh   |  Selam
    4        | Deepan   |  Chennai
    5        | Ashok    |  Coimbatore

For the above example i want to select only repeated order customer details only.

I mean now select only cus_id– 1 details, because customer one order more then one time.

This is the query am used

SELECT * 
FROM oc_order o 
LEFT JOIN oc_customer oc ON (o.customer_id = oc.customer_id) 
WHERE  (SELECT * 
        FROM oc_order 
        WHERE customer_id=o.customer_id)

Best Answer

SELECT c.*
FROM oc_customer c
JOIN oc_order o ON c.cus_id = o.cus_id 
GROUP BY c.cus_id
HAVING COUNT(o.order_id) > 1

Related Solutions

Mysql – How to profile a particularly slow or inefficient query

You didn't provide CREATE TABLE scripts; thus, it's hard to give you a specific advice; I'll try to describe general approach....

If you want to speed up this query, first of all, make sure you have indexes on the columns that define join condition, used as a filter (WHERE), or column[s] you have in GROUP BY or ORDER BY. In general, a good sign that some indexes are missed is when EXPLAIN shows "NULL" in key,key_len and "ALL" in type column (not always though; for instance if you select all rows from the table, or table is small enough, so full scan is faster than index seek + lookup). Then you may want to tweak some indexes to make them covering for this query.

Side note. The query seems to me like a BI query which normally is not executed against OLTP database. When dealing with a big data, it makes sense to build a few cubes based on data from operational db and query them instead of original data. The nature of OLTP implies high level of normalization and optimization for INSERT/UPDATE/DELETE, not for SELECTs (still possible, but queries can be very long, not clear, and quite slow).

MySQL – How to Use a Value from SELECT in a Joined Subquery

You probably have a bug: It should be ip_to >= instead of ip_to > -- to cover the case where ip_address is the same as ip_to.

You must make sure that there are no overlapping ranges in ip_information; otherwise you will occasionally get strange errors.

And, as Lee pointed out, you do need

and ipd.ip_from <= INET_ATON(c1.ip_address)

Otherwise, you could fall into a gap.

But, none of that addresses the question. You need a "correlated subquery", which cannot be done with a subquery in FROM/JOIN. So, let's do...

SELECT DISTINCT ip_address FROM customers ... This is an optimization so that we won't be repeatedly looking up the same ips.
Reach into ip_address_data to find a set of ip_to values.
Now reach back into both tables to get the rest of the info.

Something like this... (I have left out the necessary INET_ATON calls -- suggest you make your data consistent.)

First, Steps 1 and 2:

SELECT  c.ip_address, 
      ( SELECT  MIN(ip_to)
            FROM  ip_address_data
            WHERE  ip_to >= c.ip_address
            ORDER BY  ip_to
            LIMIT  1 
      ) AS ip_to
    FROM  
      ( SELECT  DISTINCT ip_address
            FROM  customers
            WHERE  ... 
      ) AS c;

See if that correctly delivers pairs of ip_address and ip_to. Yes, we have lost the customer info and ip_from; Step 3 will take care of that.

SELECT  c2..., a2...
    FROM  ( as_above ) AS b
    JOIN  customers AS c2 USING(ip_address)
    JOIN  ip_address_data AS a2 USING(ip_to)
    WHERE a2.ip_from <= b.ip_address
    GROUP BY ...
    ORDER BY ...;

Index needed:

customers: INDEX(ip_address)

One more thing... IPv6 is upon us; your table cannot handle such.

Best Answer

Related Solutions

Mysql – How to profile a particularly slow or inefficient query

MySQL – How to Use a Value from SELECT in a Joined Subquery

Related Question