Linking two times to the same table

Question

Background: I am building a picture database to manage all my photos i made. I have a lot of RAW files (negatives) and a lot of JPG-files (copies). Most of the JPG are edited versions of the RAW files but some JPG don‘t have a coressponding RAW file (e.g. I took the photo with a smartphone). Therefore a picture can consist of single JPG file or a JPG and a corresponding RAW file.

I would like to explicitly distinguish 2 cases:

1. There is no RAW file of the JPG (e.g. smartphone)
2. There is a RAW file of the photo but I didn‘t found it in my backups yet.

That‘s what my database looks like at the moment:

SELECT * FROM pictures
*------*------------*------------*--------*
| p_id | p_complete | p_negative | p_copy |
*------*------------*------------*--------*
| 1    | true       | 1          | 2      |
| 2    | true       | null       | 3      |
| 3    | false      | null       | 4      |
*------*------------*------------*--------*

SELECT * FROM files
*------*--------*
| f_id | f_path |
*------*--------*
| 1    | a.raw  |
| 2    | a.jpg  |
| 3    | b.jpg  |
| 4    | c.jpg  |
*------*--------*

What i want is to build a query to fetch all pictures. The result should be:

*----*----------*----------*-------*
| id | complete | negative | copy  |
*----*----------*----------*-------*
| 1  | true     | a.raw    | a.jpg |
| 2  | true     | null     | b.jpg |
| 3  | false    | null     | c.jpg |
*----*----------*----------*-------*

What i tried:

SELECT p_id as id,
       p_complete as complete,
       f1.f_path as negative,
       f2.f_path as copy
FROM pictures as p,
     files as f1,
     files as f2
WHERE ((f1.f_id = p.p_negative) OR (p.p_negative=null)) AND
      f2.f_id = p.p_copy

This result is:

*----*----------*----------*-------*
| id | complete | negative | copy  |
*----*----------*----------*-------*
| 1  | true     | a.raw    | a.jpg |
*----*----------*----------*-------*

what i didn‘t want.

Should I change my design, and if so, how? Or is it possible to write a query which will do want i want?

Answer 1

SELECT a.p_id id, a.p_complete complete, b.f_path negative, c.f_path copy FROM pictures a
left join files b on a.p_negative=b.f_id
left join files c on a.p_copy=c.f_id