Mysql : can’t figure out what’s wrong with this LEFT JOIN query

I would like to do a left join on the same table (licence) that only gives me licences that have no entry in the joined table. But I can’t get any result 🙁

Here is the query (quite simple actually) :

SELECT L1.licence_type, count(L1.id) as nb_licences FROM licence L1 
left JOIN licence L2 ON (L2.licence_number = L1.licence_number ) 
where L2.season = L1.season  and L2.league_id <> L1.league_id and L2.id IS NULL
GROUP BY L1.licence_type

The idea is to get only licences that has changed of league in the same year (I omitted the year’s condition on purpose, to simplify the query).

Any idea would be apreciated.

As suggested by https://stackoverflow.com/users/236345/alfabravo, here is my structure:

CREATE TABLE IF NOT EXISTS `licence` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` int(10) UNSIGNED DEFAULT NULL,
  `association_id` int(10) UNSIGNED DEFAULT NULL,
  `league_id` int(10) UNSIGNED DEFAULT NULL,
  `season` varchar(45) COLLATE utf8mb4_unicode_ci NOT NULL,
  `date_creation` datetime NOT NULL,
  `licence_number` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `licence_type` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
  `active` tinyint(1) DEFAULT NULL,
  `date_validation` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
)

As for licence_type (to aswer to https://stackoverflow.com/users/1144035/gordon-linoff), it’s just a type like “competition”, or “training”, etc…

Answer

If you use left join you should not use left joined table’s columns in where clause, in this way work as inner join, then add to condition the ON clause

SELECT L1.licence_type, count(L1.id) as nb_licences 
FROM licence L1 
left JOIN licence L2 ON (L2.licence_number = L1.licence_number ) 
    AND L2.season = L1.season  
    and L2.league_id <> L1.league_id and L2.id IS NULL
GROUP BY L1.licence_type

Advertisement

Answer