little complex sql

Question

i have album table and album permission table.

album album_id name private (values yes or no)

album permission id album_id user_id

**albums** 
album_id   name    private   
1      family      yes
2      friend      no
3      outdoor pic yes

**album permission**
id      album_id   user_id
1        3          5

now i want to get all the album list, which i have permission of. which means if album.private = no or if my id (5) exist in the permission table, then the row should be in the list, else it should not show in the list.

following should show me in the result

**albums result set** 
    album_id   name    private   
    2      friend      no
    3      outdoor pic yes   -- < this showing me, because i exist in the permission table

Answer 1

The trick is to left join on album permissions which means every album will be selected and if there is a permission record associated with it, that record will be selected too.

Then just add a where clause that says either the album must not be private, or a permission record should exist.

SELECT Albums.Album_ID, Albums.Name, Albums.Private
FROM Albums
LEFT JOIN AlbumPermissions ON Albums.Album_ID = AlbumPermissions.Album_ID 
                              AND AlbumPermissions.User_ID = 5
WHERE 
    Albmums.private == 'no'
    OR AlbumPermissions.ID IS NOT NULL

Answer 2

Using UNION:

SELECT a.album_id,
       a.name,
       a.private
  FROM ALBUMS a
 WHERE a.private = 'no'
UNION ALL
SELECT a.album_id,
       a.name,
       a.private
  FROM ALBUMS a
  JOIN ALBUM_PERMISSION ap ON ap.album_id = a.album_id
 WHERE ap.user_id = 5

Answer 3

select * from **albums** a
where a.private = 'no'
or exists (
    select 1 from  **album permission** p
    where p.album_id = a.album_id
    and p.user_id = 5
)