Kodi Community Forum
v16 What's the best way to find duplicate movies? - Printable Version

+- Kodi Community Forum (https://forum.kodi.tv)
+-- Forum: Support (https://forum.kodi.tv/forumdisplay.php?fid=33)
+--- Forum: General Support (https://forum.kodi.tv/forumdisplay.php?fid=111)
+---- Forum: OS independent / Other (https://forum.kodi.tv/forumdisplay.php?fid=228)
+---- Thread: v16 What's the best way to find duplicate movies? (/showthread.php?tid=311530)

Pages: 1 2

What's the best way to find duplicate movies? - NeoDuck - 2017-04-06

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.

RE: What's the best way to find duplicate movies? - zerocool_ie - 2017-04-07

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;


RE: What's the best way to find duplicate movies? - NeoDuck - 2017-04-08

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

(2017-04-07, 23:55)zerocool_ie Wrote: 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;


RE: What's the best way to find duplicate movies? - zerocool_ie - 2017-04-08

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

RE: What's the best way to find duplicate movies? - NeoDuck - 2017-04-08

(2017-04-08, 00:17)zerocool_ie Wrote: 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.

RE: What's the best way to find duplicate movies? - zerocool_ie - 2017-04-08

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.

RE: What's the best way to find duplicate movies? - NeoDuck - 2017-04-08

(2017-04-08, 00:36)zerocool_ie Wrote: 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?

RE: What's the best way to find duplicate movies? - zerocool_ie - 2017-04-08

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

RE: What's the best way to find duplicate movies? - NeoDuck - 2017-04-08

(2017-04-08, 01:03)zerocool_ie Wrote: Yes, but it's much more hassle than a client with a UI.

Cool, thank you!

RE: What's the best way to find duplicate movies? - NeoDuck - 2017-04-08

(2017-04-08, 01:03)zerocool_ie Wrote: 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'


RE: What's the best way to find duplicate movies? - zerocool_ie - 2017-04-08

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;


RE: What's the best way to find duplicate movies? - NeoDuck - 2017-04-08

(2017-04-08, 01:26)zerocool_ie Wrote: 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!!

RE: What's the best way to find duplicate movies? - zerocool_ie - 2017-04-08

Good stuff. Enjoy cleaning your dupes out now Smile

RE: What's the best way to find duplicate movies? - NeoDuck - 2017-12-18

(2017-04-08, 01:32)zerocool_ie Wrote: Good stuff. Enjoy cleaning your dupes out now Smile
 Hi, I just recently upgraded to Kodi 17.x and this query doesn't seem to work anymore, it results in this?
Quote: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. */


RE: What's the best way to find duplicate movies? - zerocool_ie - 2017-12-18

(2017-12-18, 06:55)NeoDuck Wrote:  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;