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…
Advertisement
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