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

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);

CodePudding user response：

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

CodePudding user response：

Start with a subquery to find all the ClientCode values that have ten or more items in your location table. The aggregating subquery does the counting.

                SELECT ClientCode
                  FROM location
                 GROUP BY ClientCode
                HAVING COUNT(*) >= 10

Then INNER JOIN that subquery in your main query to filter out all but the frequent customers.

SELECT client.ClientCode, client.ClientNom, (location.LocationRef)
  FROM client
 INNER JOIN location ON client.ClientCode = location.ClientCode
 INNER JOIN (
                 SELECT ClientCode
                   FROM location
                  GROUP BY ClientCode
                HAVING COUNT(*) >= 10
) frequent_clients  ON client.ClientCode = frequent_clients.ClientCode;

If you want just one row per client in your result set, try this

SELECT client.ClientCode, client.ClientNom, COUNT(*) number_of_rentals
  FROM client
 INNER JOIN location ON client.ClientCode = location.ClientCode
 INNER JOIN (
                 SELECT ClientCode
                   FROM location
                  GROUP BY ClientCode
                HAVING COUNT(*) >= 10
    ) frequent_clients
          ON client.ClientCode = frequent_clients.ClientCode
GROUP BY client.ClientCode, client.ClientNom

Fiddle here.