MySQL - get all unique values of a column, check if has a specific value
- by gamers2000
First off - apologies for the poor title, I have no idea how to describe it in a one-liner.
I have a table - snippet is below.
mysql> select * from playlistfiles;
+-----------------------+--------------+-----------+
| FileName | PlaylistName | FileIndex |
+-----------------------+--------------+-----------+
| File1 | Image1 | 0 |
| File1 | Video1 | 2 |
| File2 | Video1 | 0 |
| File3 | Video1 | 1 |
| File4 | Image1 | 1 |
| File4 | Video1 | 3 |
+-----------------------+--------------+-----------+
6 rows in set (0.00 sec)
What I need to do is to get all the FileNames and whether the file is in a playlist or not, as well as order them by FileIndex i.e. for the Image1 playlist, the output should be
+-----------------------+------------+-----------+
| FileName | InPlaylist | FileIndex |
+-----------------------+------------+-----------+
| File1 | 1 | 0 |
| File2 | 0 | Null |
| File3 | 0 | Null |
| File4 | 1 | 1 |
+-----------------------+------------+-----------+
and Video1 would be
+-----------------------+------------+-----------+
| FileName | InPlaylist | FileIndex |
+-----------------------+------------+-----------+
| File2 | 1 | 0 |
| File3 | 1 | 1 |
| File1 | 1 | 2 |
| File4 | 1 | 3 |
+-----------------------+------------+-----------+
In short, I need to be able to get all the unique FileNames from the table, and check if it is in a given table and if so, order it by FileIndex.
