[tjs4ever]

Hi all,

I am an absolute novice with mysql and am having a hard time modifying the below query. I am hoping there might be a SQL ninja on here that can help me out.

I have the below SQL which is 99% working for my needs, this helps me find duplicates in Kodi but there are a large number of false-positives that I want the select statement to ignore.

sql:
.header on

.mode csv

.output //MyServer/MyShare/Misc/Scripts/DuplicateMovies/KODI/SQL_DUPES_KODI_EPISODES_RESULTS.csv

select

            tvshow.c00 show,

            episode.c12 s,

            episode.c13 e,

            episode.c00 title,

            count(*) num,

            group_concat(path.strPath || files.strFilename) locations

from files

join episode on episode.idFile = files.idFile

join path on files.idPath = path.idPath

join tvshow on episode.idShow = tvshow.idShow

group by episode.idShow, s, e having num > 1;

The problem I am facing - I would like to include something like this in the first select statement so that filenames with '%CD1' will be ignored:


WHERE strfilename NOT LIKE '%CD1%' and strfilename NOT LIKE '%CD2%'


I feel like I am missing something obvious, I keep getting syntax errors.

Appreciate any insights,
TJ

[tjs4ever]

double edit. I got this working, full SQL below:

sql:
.header on

.mode csv

.output //MyServer/MyShare/Misc/Scripts/Scheduled/KODI/SQL_DUPES_KODI_EPISODES_RESULTS.csv

select

            tvshow.c00 show,

            episode.c12 s,

            episode.c13 e,

            episode.c00 title,

            count(*) num,

            group_concat(path.strPath || files.strFilename) locations

from files

join episode on episode.idFile = files.idFile

join path on files.idPath = path.idPath

join tvshow on episode.idShow = tvshow.idShow

where files.strFilename NOT LIKE '%CD1%'

group by episode.idShow, s, e having num > 1;

[jbinkley60]

You got it right.  The WHERE parameters for the initial SELECT come at the end after the conditional join statements.


Jeff

[tjs4ever]

Thanks Jeff, I feel like such a dummy. SQL is something I only dabble in a few times per year. Wondering if you can help me with this other SQL - this is the equivalent query to find duplicates, but I'm running it against my PlexDB (yes I know this is not a plex forum)


My original SQL:

sql:
.header on

.mode csv

.output //MyServer/MyShare/Misc/Scripts/DuplicateMovies/PLEX/SQL_DUPES_PLEX_RESULTS.csv



SELECT metadata.title AS Title, parts.file AS Path FROM media_parts parts

INNER JOIN media_items items ON items.id=parts.media_item_id

INNER JOIN metadata_items metadata ON metadata.id=items.metadata_item_id

INNER JOIN (

    SELECT guid FROM metadata_items

    WHERE metadata_type=1

    GROUP BY guid

    HAVING COUNT(*) > 1

) duplicates ON duplicates.guid=metadata.guid

ORDER BY title, metadata.guid;

I want to add something like

sql:
'where parts.file NOT LIKE '%CD1%' AND parts.file NOT LIKE '%CD2%''

on the second last line of the SQL, but the order of operations is wrong if I do it this way.
I want to do these exclusions BEFORE doing the count, otherwise the results aren't 100% what I am after.

I hope I'm not breaking any rules but I have a thread opened over at plex forums : https://forums.plex.tv/t/need-help-again...e/866336/7

[kcook_shield]

Not tested, but maybe:

SELECT metadata.title AS Title, parts.file AS Path
FROM media_parts parts
INNER JOIN media_items items ON items.id = parts.media_item_id
INNER JOIN metadata_items metadata ON metadata.id = items.metadata_item_id
WHERE parts.file NOT LIKE '%CD1%'
  AND parts.file NOT LIKE '%CD2%'
  AND metadata.guid IN (
      SELECT guid
      FROM metadata_items
      WHERE metadata_type = 1
      GROUP BY guid
      HAVING COUNT(*) > 1
  )
ORDER BY title, metadata.guid;

[jbinkley60]

on the second last line of the SQL, but the order of operations is wrong if I do it this way.
I want to do these exclusions BEFORE doing the count, otherwise the results aren't 100% what I am after.

I hope I'm not breaking any rules but I have a thread opened over at plex forums : https://forums.plex.tv/t/need-help-again...e/866336/7

What @kcook_shield posted looks like it should work.  On a related topic I can see where MySQL wouldn't have dupe detection since Kodi doesn't but am a bit surprised that Plex doesn't.  I built this functionality into the Mezzmo Kodi addon a couple of years ago, along with a tracker so you can see them by day (i.e. when they showed up ii your library).  Seems like a bit of a glaring omission.  Also I am a bit intrigued as to why you are running both MySQL and Plex ?  There is a lot of overlap between the two or are both queries for the same Plex database ?


Thanks,

Jeff

[tjs4ever]

@kcook_shield this is perfect.

I actually ended up with the below. For reasons I don't understand there is still output which doesn't belong, but I think more to do with plex DB than anything.


SELECT metadata.title AS Title, parts.file AS Path
FROM media_parts parts
INNER JOIN media_items items ON items.id = parts.media_item_id
INNER JOIN metadata_items metadata ON metadata.id = items.metadata_item_id
WHERE parts.file NOT LIKE '%CD1%'
  AND parts.file NOT LIKE '%CD2%'
  AND parts.file NOT LIKE 'M:\3D\%'
  AND metadata.guid IN (
      SELECT guid
      FROM metadata_items
      WHERE metadata_type = 1
      GROUP BY guid
      HAVING COUNT(*) > 1
  )
ORDER BY title, metadata.guid;




Answered my questions and then some

[tjs4ever]

Hey @jbinkley60,

I haven't heard of mezzmo until just now, definitely will be doing some further reading - it looks like it does it all.

I'm running a plex server and kodi on separate machines, just the standard local DB for both, they share a mapped drive with the media. At the moment both are windows boxes.

I have duplicate entries creep up on both DBs over time; in most cases it's just a scraping issue, sometimes I have to change filenames or dates in filenames to make things scan in 100%. Sometimes the two never play along perfectly and I just pick one over the other.

The two SQL - one for plex one for kodi, the kodi one and its results I am way more confident in
For plex DB there is even a special 'Plex SQLite.exe' that you need to use.