# SQLite queries **URL:** \ **Category:** Dev/API Corner\ **Tags:** other-dev\ **Created:** [January 24, 2017, 12:07am UTC](https://forums.plex.tv/t/sqlite-queries/177050 "2017-01-24T00:07:12Z")\ **Posts on this page:** 4\ **Page:** 1
**Author:** ![jmbrunod](https://avatars.discourse-cdn.com/v4/letter/j/f0a364/32.png) [@jmbrunod](https://forums.plex.tv/u/jmbrunod)\ **Post date:** [January 24, 2017, 12:07am UTC](https://forums.plex.tv/t/sqlite-queries/177050/1 "2017-01-24T00:07:12Z")
Hello, I have created SQLite queries to get a list of my movies and TV shows. Has I tooked me a while to understand the database schema, the SQLite SQL and get this queries to work, I decided to share this and I hope I’m in the correct place. These queries are adapted to my database (library ID for example), so you’ll need to adapt them to your database. Movie List `SELECT film.title as Titre, group_concat(tags.tag, ', ') AS Tags, librairie.name, media.duration/60000 AS duration, media.width, media.height, printf("%.4f",media.frames_per_second) AS framerate, media.bitrate/1000 AS birate, media.video_codec, media.size/1048576 AS SIZE, film.summary FROM metadata_items film JOIN media_items media ON media.metadata_item_id = film.id JOIN library_sections librairie ON librairie.id = film.library_section_id JOIN taggings ON taggings.metadata_item_id = film.id JOIN tags on tags.id = taggings.tag_id WHERE film.metadata_type = 1 AND librairie.name IN ("Animation","Concerts","Documentaires","Films","Spectacles","Théatre") AND tags.tag_type = 1 GROUP BY film.id ORDER BY librairie.name, film.title` TV Show Episode List `SELECT serie.title as Série, group_concat(tags.tag, ', ') AS Tags, saison.[index] as Saison, episode.[index] as Episode, episode.title as Titre, librairie.name, media.duration/60000 AS duration, media.width, media.height, printf("%.4f",media.frames_per_second) AS framerate, media.bitrate/1000 AS birate, media.video_codec, media.size/1048576 AS SIZE, serie.summary FROM metadata_items episode JOIN metadata_items saison ON saison.id = episode.parent_id JOIN metadata_items serie ON serie.id = saison.parent_id JOIN media_items media ON media.metadata_item_id = episode.id JOIN library_sections librairie ON librairie.id = episode.library_section_id JOIN taggings ON taggings.metadata_item_id = serie.id JOIN tags on tags.id = taggings.tag_id WHERE episode.metadata_type = 4 AND librairie.id = 2 AND tags.tag_type = 1 GROUP BY episode.id ORDER BY serie.title, saison.[index], episode.[index]` Please feel free to share corrections or improvments ! ---
**Author:** ![misctv](https://avatars.discourse-cdn.com/v4/letter/m/3bc359/32.png) [@misctv](https://forums.plex.tv/u/misctv)\ **Post date:** [November 19, 2017, 9:18pm UTC](https://forums.plex.tv/t/sqlite-queries/177050/2 "2017-11-19T21:18:33Z")
Very cool! I know a little sql but apparently not enough to add the tags\_collection to this. I guess a join drops columns not referenced? Ideally I want to list TV shows that do not have a Collection tag assigned… ---
**Author:** ![misctv](https://avatars.discourse-cdn.com/v4/letter/m/3bc359/32.png) [@misctv](https://forums.plex.tv/u/misctv)\ **Post date:** [November 20, 2017, 1:37am UTC](https://forums.plex.tv/t/sqlite-queries/177050/3 "2017-11-20T01:37:47Z")
OK, I finally figured it out: List all shows: select title from metadata\_items where metadata\_items.parent\_id is NULL and library\_section\_id=7 order by title; List shows with collection tag(s) and what the tags are: select title, tag from metadata\_items join taggings taggings join tags where taggings.metadata\_item\_id = metadata\_items.id AND metadata\_items.parent\_id is NULL and library\_section\_id=7 AND taggings.tag\_id=tags.id AND tag\_type=2 order by title, tag; Compare those to list shows with no collection tags: SELECT title FROM metadata\_items WHERE title NOT IN (select distinct title from metadata\_items join taggings taggings join tags where taggings.metadata\_item\_id = metadata\_items.id and library\_section\_id=7 AND taggings.tag\_id=tags.id AND tag\_type=2) AND metadata\_items.parent\_id is NULL and library\_section\_id=7; You’ll need to use whatever library\_section\_id number corresponds to shows on your plex box, of course. ---
**Author:** ![system](https://global.discourse-cdn.com/plex/original/3X/2/a/2acb9765406f63293d357b4ec509ec39aa28f2ad.png) [@system](https://forums.plex.tv/u/system)\ **Post date:** [January 8, 2020, 8:01pm UTC](https://forums.plex.tv/t/sqlite-queries/177050/4 "2020-01-08T20:01:51Z")