SQL Left Join Problem

I'm creating a checklist-style program, where files are assigned to checklists, and you check items off of checklists for certain files. I'm trying to run a query that returns all files that are READY for a certain checklist item (items are ordered).

So for example, I am trying to see which files are ready for checklist item number 3, so I need to find all files that have been marked as checked for item number 2, BUT NOT for item number 3.

I would also prefer to NOT use sub-queries, even though I know sub-queries would solve this problem (that's why this is taking so long), since this query will be run for every checklist item, and I feel that sub-queries here would affect performance negatively开发者_如何学运维.

Here is my query so far:

SELECT
    DISTINCT f.filename, f.id
FROM
    files f LEFT JOIN checklist_item_checklist cic1 LEFT JOIN checklist_check cc1 ON
    cc1.checklist_item_checklist_id = cic1.checklist_item_checklist_id ON
    cc1.file_id != f.id,checklist_item_checklist cic2,
    checklist_check cc2
WHERE
    cic1.checklist_item_checklist_id = 2 AND
    cic2.order_number = cic1.order_number - 1 AND
    cic1.checklist_id = cic2.checklist_id AND
    cc2.checklist_item_checklist_id = cic2.checklist_item_checklist_id AND
    cc2.file_id = f.id

The table structure is:

files

• id (PK)
• filename

checklist_item_checklist

• checklist_item_checklist_id (PK)
• order_number

checklist_check

• file_id (FK to files.id)
• checklist_item_checklist_id (FK to checklist_item_checklist.checklist_item_checklist_id)

Thanks!

You have lots of syntax errors in your query. I believe you need a subquery.

This should work:

SELECT f.id, f.filename
FROM files f 
JOIN checklist_check cc ON cc.file_id = f.id
JOIN checklist_item_checklist cic ON cic.checklist_item_checklist_id = cc.checklist_item_checklist_id AND cc.order_number = 2
WHERE f.id not in(
    SELECT f.id,
    FROM files f 
    JOIN checklist_check cc ON cc.file_id = f.id
    JOIN checklist_item_checklist cic ON cic.checklist_item_checklist_id = cc.checklist_item_checklist_id AND cc.order_number = 3)