Selecting rows with references across tables in SQLite 3

I have a SQLite photo/album database with 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:

photo1.jpg
photo3.jpg

However, the required result would be:

photo1.jpg
photo3.jpg
photo4.jpg

The problem is 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!

First of your query retursn photo 1 and 3, so I assume that this is correct.

So you can join and look for null values as this will have no album to join on.

SELECT file
FROM photos LEFT OUTER JOIN (relation join albums on relation.album = albums.id)
            ON relation.photo = photos.id
WHERE albums.hide = 0 OR albums.id IS NULL