find data that couldn't exist working with 2 tables

Question

3 tables:

IMAGES [IDim, title, descr, etc.]
USERS [IDus, name, surname, etc.]
OPERATION [ID, IDim, IDus, status, reserveDate, etc.]

I need a query that gets all rows of IMAGES and adds the status read from the OPERATION table. The status field gets value 'READY' or 'LOST' only sometimes (and in that moment a link between the IMAGES and the OPERATION tables is created).

I tried this incomplete and wrong query:

SELECT i.IDim, i.title, i.descr, o.status
FROM IMAGES AS i, OPERATION AS o
WHERE i.IDim = o.IDim

This fetches only the ones in the OPERATION table, but It's possible that some images are not inserted there.

A possibility is to create a reference in OPERATION every time a new item is inserted in IMAGES, setting the status field as 'NONE', for example, but I'd like to avoid it.

How do I write this?

Answer 1

You need a LEFT JOIN for that.

JOINs are around for 30 years now, you should really start using them all the time

SELECT i.IDim, i.title, i.descr, o.status
FROM IMAGES AS i
 LEFT JOIN OPERATION AS o
ON i.IDim = o.IDim