MySQL INNER JOIN query to get client information having at least 10 rentals

I am trying to query the code, name, and the number of rentals of customers who have made at least ten rentals.

My query:

Select client.ClientCode, client.ClientNom, (location.LocationRef)
FROM client
  INNER JOIN location ON client.ClientCode = location.ClientCode
WHERE location.LocationRef > 9
GROUP BY location.ClientCode;

Output:

ClientCode | ClientNom | LocationRef
-----------+-----------+------------
12874      | Alex      | 10

It doesn't work. It shows me the first LocationRef beyond 10 but not the number of rentals made by the client.

I test with a COUNT in my query :

SELECT client.ClientCode, client.ClientNom, Count(location.LocationRef) 
  FROM client 
    INNER JOIN location 
      ON client.ClientCode = location.ClientCode WHERE location.LocationRef > 9 
GROUP BY location.ClientCode;

This is what i got :

ClientCode | ClientNom | Count(location.locationRef)
-----------+-----------+----------------------------
12874      | Alex      | 5

This is not what I want, because my client has made 13 rentals and it only shows me the number of rentals beyond the 9th rental.

Script to install my database:

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;

SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

CREATE TABLE `client` (
  `ClientCode` int(11) NOT NULL,
  `ClientNom` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `client` (`ClientCode`, `ClientNom`) VALUES
(123, 'Gaetan'),
(12874, 'Alex'),
(12875, 'Max');

CREATE TABLE `location` (
  `LocationRef` int(11) NOT NULL,
  `Immatriculation` varchar(11) NOT NULL,
  `ClientCode` int(11) NOT NULL,
  `MontantLocation` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `location` (`LocationRef`, `Immatriculation`, `ClientCode`, `MontantLocation`) VALUES
(1, 'AA-229-AA', 12874, 123), (2, 'AA-229-AB', 12875, 156),
(3, 'BA-229-AA', 12874, 700), (4, 'AB-229-AA', 12874, 678),
(5, 'AA-229-AB', 12874, 987), (6, 'AA-229-AB', 12874, 980),
(7, 'AB-229-AA', 12874, 567), (8, 'AA-229-AA', 12874, 7789),
(9, 'AA-229-AB', 12874, 567), (10, 'AA-229-AB', 12874, 456),
(11, 'AA-229-AA', 12874, 566), (12, 'AB-229-AA', 12874, 700),
(13, 'AA-229-AA', 12874, 899), (14, 'AA-229-AB', 12874, 67);

Solution

The query condition WHERE location.LocationRef > 9 is not doing what you expect. It's limiting your query to only access the 10th and later entries into the location table. Entries where LocationRef equals 1 - 9 are ignored.

And Count(location.LocationRef) is in the wrong part of the query.

Rather, put the COUNT and GROUP BY in an ad hoc, temporary table querying the location table, INNER JOIN'ed with the client table, to get client information and count how many records there are over a certain number of client records, then limit the querying of client information to only those who have at least 10 entries:

SELECT loc.ClientCode, loc.cnt, client.ClientNom 
  FROM client 
    INNER JOIN (
      SELECT ClientCode, COUNT(ClientCode) cnt 
        FROM location 
        GROUP BY clientCode
    ) loc ON client.ClientCode = loc.ClientCode
  WHERE loc.cnt > 9 /* at least 10 entries */
;

Output:

ClientCode | cnt | ClientNom
-----------+-----+----------
12874      | 13  | Alex

The temporary table query:

SELECT ClientCode, COUNT(ClientCode) cnt 
  FROM location 
  GROUP BY clientCode

...uses a combination of COUNT AND GROUP BY to determine how many records there are for each ClientCode.

And results in the output:

ClientCode | cnt
-----------+----
12874      | 13
12875      | 1

Now this becomes the temporary table that can be INNER JOIN'ed with the client table, using the ClientCode field, to get the client information: ON client.ClientCode = loc.ClientCode.

Also the calculated field COUNT(ClientCode), in the temp table query, is aliased cnt for access outside the temp table query, as in: loc.cnt and WHERE loc.cnt > 9.

The temporary table must be named. That name is then used to access it's values outside of the temporary table query itself:

INNER JOIN ( /* temp query here */ ) loc ON client.ClientCode = loc.ClientCode

In this case using the name loc and referencing fields like this: loc.ClientCode.

And, finally, it's the primary query, or outside query, where the results are limited to only those clients with a count (cnt) of more than 9 (at least 10) entries in the loc table:

SELECT loc.ClientCode, loc.cnt, client.ClientNom
  FROM client
  /* INNER JOIN here, named loc */
WHERE loc.cnt > 9

Try it here: https://onecompiler.com/mysql/3yfwuunrs