MySQL query help on joins and counting most occurance

I have two tables, client_table and order_table

Entries in the client_table are unique with id as primary key In the order_table, the order_id is the primary key and also a client_id field. The client_id is the primary key of the client_table. There can be multiple entries in the order_table that contain the same client_id. So it is a one-to-many relationship.

I'm trying to come up with a query that produce the most occurring client_id in the order_table

I have tried the following queries.

SELECT a.id FROM `client_table` as a  inner join order_开发者_开发知识库table as b GROUP BY a.id ORDER BY count(b.client_id)

So I'm looking for a result of client_id which have the most orders. I suppose I only need the order_table and don't need the client_table at all right ?

---

SELECT b.client_id, count(*) AS Clients
FROM order_table AS b 
GROUP BY b.client_id 
ORDER BY Clients DESC

No need for a join if you only want the clients id, like you said. Also, if you want to join, like in your example query, you need to specify on which column with a ON clause.

... inner join order_table as b on a.id=b.client_id ...

---

Yes, you don't need the client table at all:

select
    client_id,
    count(order_id) order_count
from
    order_table
group by
    client_id
order by
    count(order_id) DESC

---

you can try:

SELECT a.id FROM `client_table` as a  left join order_table as b ON a.id = b.client_id GROUP BY a.id ORDER BY count(b.client_id) desc

Use DESC at ORDER BY to get most occurances. Right now you only get the lowest occurances, and try changing INNER JOIN to LEFT JOIN.