Can you help me with my sql request (MYSQL server)-CodePudding

i need help for my sql request that contain inner join. Here is the script to install my database (if you want) :

    -- phpMyAdmin SQL Dump
-- version 5.2.0
-- https://www.phpmyadmin.net/
--
-- Hôte : 127.0.0.1
-- Généré le : lun. 12 sep. 2022 à 15:15
-- Version du serveur : 10.4.24-MariaDB
-- Version de PHP : 7.4.29

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 */;

--
-- Base de données : `edc_dev_2003r`
--

-- --------------------------------------------------------

--
-- Structure de la table `bareme`
--

CREATE TABLE `bareme` (
  `Tranche` int(11) NOT NULL,
  `MontantTranche` int(11) NOT NULL,
  `Points` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------

--
-- Structure de la table `client`
--

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

--
-- Déchargement des données de la table `client`
--

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

-- --------------------------------------------------------

--
-- Structure de la table `location`
--

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;

--
-- Déchargement des données de la table `location`
--

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

-- --------------------------------------------------------

--
-- Structure de la table `vehicule`
--

CREATE TABLE `vehicule` (
  `Immatriculation` varchar(11) NOT NULL,
  `Catégorie` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Déchargement des données de la table `vehicule`
--

INSERT INTO `vehicule` (`Immatriculation`, `Catégorie`) VALUES
('AA-229-AA', 'A'),
('AA-229-AB', 'B'),
('AB-229-AA', 'C'),
('BA-229-AA', 'D');

--
-- Index pour les tables déchargées
--

--
-- Index pour la table `bareme`
--
ALTER TABLE `bareme`
  ADD PRIMARY KEY (`Tranche`);

--
-- Index pour la table `client`
--
ALTER TABLE `client`
  ADD PRIMARY KEY (`ClientCode`);

--
-- Index pour la table `location`
--
ALTER TABLE `location`
  ADD PRIMARY KEY (`LocationRef`),
  ADD KEY `ClientCode` (`ClientCode`),
  ADD KEY `Immatriculation` (`Immatriculation`);

--
-- Index pour la table `vehicule`
--
ALTER TABLE `vehicule`
  ADD PRIMARY KEY (`Immatriculation`);

--
-- Contraintes pour les tables déchargées
--

--
-- Contraintes pour la table `location`
--
ALTER TABLE `location`
  ADD CONSTRAINT `ClientCode` FOREIGN KEY (`ClientCode`) REFERENCES `client` (`ClientCode`),
  ADD CONSTRAINT `Immatriculation` FOREIGN KEY (`Immatriculation`) REFERENCES `vehicule` (`Immatriculation`);
COMMIT;

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

(Sorry by advance my database is in french)

The query i want to do is :

"The code and name as well as the number of rentals of customers who have made at least ten rentals."

My current query is :

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;

and as you can understand it doesn't work cause it show me the first "LocationRef" beyond 10 but not the number of rental made by the client.

If some of you can help me on this issue.

Thanks in advance.

CodePudding user response：

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;

And look what i got :

response display

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

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

Fiddle here.