Selecting rows with references across tables in SQLite 3
- by ChristianK
Hey there,
I have a problem with a SQLite photo/album database. The database contains 3 tables:
Albums
id name hide
--------------------------
1 Holiday 2010 1
2 Day Trip 0
Photos
id file
-----------------
1 photo1.jpg
2 photo2.jpg
3 photo3.jpg
4 photo4.jpg
Relation (connects photos with albums)
album photo
-----------------
1 1
1 2
2 3
2 1
A photo can be assigned to zero, one or several albums. Each album has a column 'hide' that indicates, whether the photos of this album should be ignored.
I'm trying to find a SELECT query that returns all photos that are not assigned to an album + all the photos that are in albums which are not hidden (i.e. that have their 'hide' value set to 0).
I came up with a query that selects photos in visible albums, but I don't know how to include the photos that are not assigned to an album.
SELECT file FROM photos, albums, relation WHERE photos.id = relation.photo AND albums.id = relation.album AND albums.hide = 0
This query returns
photo3.jpg
and the required result would be
photo3.jpg
photo4.jpg
because photo4.jpg is not assigned to album in the Relation table.
Do you know how to solve this?
Thank you very much for your help!