[NeoDuck]

Hi, I have 7 drives in my Kodi library. I was wondering if anyone has any good ways to find duplicate movies in the library?

For example, an HC version of a movie and a 1080p brrip version of the same movie.

What I currently do is to simlpy scroll through the library and manually search for duplicates.

[zerocool_ie]

if you're SQL-savvy, this is what I use for Krypton dupe finding

Code:

SELECT c00 as 'Title',
         strPath AS 'Path',
         strfilename AS 'File'
FROM movie_view a
JOIN (
SELECT uniqueid_value
FROM movie_view
GROUP BY uniqueid_value
HAVING COUNT(*) > 1) b ON a.uniqueid_value = b.uniqueid_value
ORDER BY strfilename ASC;

[NeoDuck]

Does this make a list of duplicates and their file locations?

if you're SQL-savvy, this is what I use for Krypton dupe finding

Code:

SELECT c00 as 'Title',
         strPath AS 'Path',
         strfilename AS 'File'
FROM movie_view a
JOIN (
SELECT uniqueid_value
FROM movie_view
GROUP BY uniqueid_value
HAVING COUNT(*) > 1) b ON a.uniqueid_value = b.uniqueid_value
ORDER BY strfilename ASC;

[zerocool_ie]

Yep... Movie Name, Folder location, File Name

[NeoDuck]

Yep... Movie Name, Folder location, File Name

Thank you!

So, do I just type in each command on each line one at a time?

Where does the output file save to?

Sorry for the basic questions, I'm a MySQL noob.

[zerocool_ie]

are you using a shared MySQL database or a local Kodi db?

- For shared, grab an SQL client like HeidiSQL and make a connection to your server
(if you're using MySQL, you'd have configured the settings in advancedsettings.xml (wiki) )
- For local, grab an SQLite client like SQLiteSpy and open up your MyVideosXX.db file from userdata (wiki)

Paste the whole chunk of code above into the sql client and click "Go" or "Execute"

It'll print out the list on screen for you.

[NeoDuck]

are you using a shared MySQL database or a local Kodi db?

- For shared, grab an SQL client like HeidiSQL and make a connection to your server
(if you're using MySQL, you'd have configured the settings in advancedsettings.xml (wiki) )
- For local, grab an SQLite client like SQLiteSpy and open up your MyVideosXX.db file from userdata (wiki)

Paste the whole chunk of code above into the sql client and click "Go" or "Execute"

It'll print out the list on screen for you.

I'm using a shared MySQL database. Can I use the MySQL 5.5 Command Line Client?

[zerocool_ie]

Yes, but it's much more hassle than a client with a UI.

[NeoDuck]

Yes, but it's much more hassle than a client with a UI.

Cool, thank you!

[NeoDuck]

Yes, but it's much more hassle than a client with a UI.

Actually, could I get some more detailed instructions?

I've installed HeidiSQL and connected to my MySQL host.

Then, I selected myvideos99, in the left column window.

I tried pasting the chunk of code into the Query tab but then it returned:

Code:

SQL Error (1054): Unknown column 'uniqueid_value' in 'field list'

[zerocool_ie]

Ah, you're on Kodi 16 - the column name containing the IMDB IDs is different in Kodi 17.

This should work:

Code:

SELECT c00 as 'Title',
         strPath AS 'Path',
         strfilename AS 'File'
FROM movie_view a
JOIN (
SELECT c09
FROM movie_view
GROUP BY c09
HAVING COUNT(*) > 1) b ON a.c09 = b.c09
ORDER BY strfilename ASC;

[NeoDuck]

Ah, you're on Kodi 16 - the column name containing the IMDB IDs is different in Kodi 17.

This should work:

Code:

SELECT c00 as 'Title',
         strPath AS 'Path',
         strfilename AS 'File'
FROM movie_view a
JOIN (
SELECT c09
FROM movie_view
GROUP BY c09
HAVING COUNT(*) > 1) b ON a.c09 = b.c09
ORDER BY strfilename ASC;

Awesome, that worked! Thank you!!

[zerocool_ie]

Good stuff. Enjoy cleaning your dupes out now

[NeoDuck]

Good stuff. Enjoy cleaning your dupes out now

 Hi, I just recently upgraded to Kodi 17.x and this query doesn't seem to work anymore, it results in this?

SELECT c00 as 'Title',
         strPath AS 'Path',
         strfilename AS 'File'
FROM movie_view a
JOIN (
SELECT c09
FROM movie_view
GROUP BY c09
HAVING COUNT(*) > 1) b ON a.c09 = b.c09
ORDER BY strfilename ASC;
/* Affected rows: 0  Found rows: 0  Warnings: 0  Duration for 1 query: 0.000 sec. */

[zerocool_ie]

 Hi, I just recently upgraded to Kodi 17.x and this query doesn't seem to work anymore, it results in this?

@NeoDuck  This is what I'm currently using:

Movies

sql:
SELECT c00, dateadded AS DateAdded,

         'Dupe Movie' AS description,

 strfilename AS FILE

FROM movie_view a

JOIN (

SELECT uniqueid_value

FROM movie_view

GROUP BY uniqueid_value

HAVING COUNT(*) > 1) b ON a.uniqueid_value = b.uniqueid_value

ORDER BY strfilename ASC;

TV

sql:
SELECT

 'Dupe Epsiode' AS description,

 y.strFileName

FROM episode_view Y

INNER JOIN (

SELECT

 c12,c13,idShow, COUNT(*) AS CountOf

FROM episode_view

GROUP BY idShow,c12,c13

HAVING COUNT(*)>1

) dt ON y.c12=dt.c12 AND y.c13=dt.c13 AND y.idShow=dt.idShow;