Question: This query:

```
SELECT order_id, count(shipments.order_id) 
    FROM shipments
    GROUP by order_id
    ORDER by count(shipments.order_id) DESC
    LIMIT 25

```

Results:

```
ID   Order ID   Count
    1   7   70
    2   1730    65
    3   359 50

```

It returns a list of order\_id's that occur the most frequently with a count of how many times they occur. When I attempt to link it with the 'users' table however, to return the corresponding user's name and email address, I get back completely different results:

```
SELECT users.first_name, users.email, count(shipments.order_id) 
    FROM users, shipments
    WHERE shipments.order_id = users.current_order_id
    GROUP by users.first_name, users.email
    ORDER by count(shipments.order_id) DESC
    LIMIT 25

```

Results

```
ID     Name    Email    OrderID   Count
1   Jane    xxx@hotmail.com 1264    31
2   Tom yyy@yahoo.co.uk 98  28
3   Foo nnn@gmail.com   3203    28

```

P.S. The Order ID used in shipments is the ID used for the recurring (current) order that sits in User.

Answer: 
Try left joining the user table to the shipment count

```
    SELECT users.first_name, users.email, count(shipments.order_id) 
    FROM shipments
    LEFT JOIN USERS ON SHIPMENTS.ORDER_ID=USERS.ORDER_ID
    WHERE shipments.order_id = users.current_order_id
    GROUP by users.first_name, users.email
    ORDER by count(shipments.order_id) DESC

```

I have also been successful doing something like this: 

```
    SELECT order_id, count(shipments.order_id) 
    into #SHIP
    FROM shipments
    GROUP by order_id
    ORDER by count(shipments.order_id) DESC
    LIMIT 25

    SELECT users.first_name, users.email, (#ship.order_id) 
    FROM #ship
    LEFT JOIN USERS ON #ship.ORDER_ID=USERS.ORDER_ID
    GROUP by users.first_name, users.email
    ORDER by count(shipments.order_id) DESC

```
Try doing an INNER JOIN between users and the first query instead:

```
SELECT u.first_name, u.email, a.orderCount
FROM users u
INNER JOIN (
    SELECT order_id, count(shipments.order_id) AS orderCount
    FROM shipments
    GROUP by order_id
) a
ON a.order_id = u.current_order_id
ORDER BY a.orderCount DESC
LIMIT 25;

```