Keep Items That Have No Active Loan

An active-loan report starts from loans, so available items have no row to contribute. The folding table is missing even though its only loan has been returned. The projector screen and repair kit are missing because they have no loan history at all.

For an availability question, start from the table whose rows must all survive: items.

Replace report.sql with this selected shape and order:

SELECT
    items.asset_tag,
    items.name AS item_name,
    loans.id AS loan_id
FROM items
ORDER BY items.asset_tag;

Then add this join before ORDER BY:

LEFT JOIN loans
    ON loans.item_id = items.id
    AND loans.returned_on IS NULL

A left join keeps every row from the table on its left. When the ON condition finds an active loan, SQLite adds that loan’s columns. When it finds no active match, the item stays and the loan columns in the result are NULL.

The active test belongs inside ON. It says which loan rows are allowed to match an item. This detail protects the folding table: its returned history does not match, but the left join still keeps the item itself.

Press Run. All five asset tags should appear. The drill and projector have loan IDs. The other three show NULL:

IT-200 | Folding table | NULL
IT-400 | Projector screen | NULL
IT-500 | Repair kit | NULL

If returned_on IS NULL moved to a later WHERE clause, the returned folding-table row would be matched first and then filtered out. It would disappear instead of becoming an unmatched item. Keep the active condition beside the relationship inside ON.

These examples have at most one active loan per item. A left join returns one row for each match, so two active loans for the same item would produce two rows for that item. Later, the checkout operation will enforce the single-active-loan rule for our single-user program.

Submit includes an active item, a returned-only item, and an item with no history. It expects each item exactly once. This report now shows the full catalog and makes the missing active match visible. That missing match is exactly what you need to select only available items.