I have a parent table and a child table.
For my delete, the details I need to relate to are spread across the two tables.
parent table holds the business id and content_category whilst the child table stores the relevant dates.
does this query structure (with a sub-select), fit the bill or is there a simpler more efficient way? Is there a principle that can apply to the decision-making process?
I dislike asking for help with vaguely or quite similar queries when I reckon there must be a rule or principle which 'dictates' key criteria with regard to sub selects or specific joins etc.
delete from file_sequencer
WHERE file_id IN ( SELECT file_id
FROM file_sequencer as fs
JOIN file_dates as fd
ON fd.file_id = fs.file_id
WHERE fs.business_id = ?
AND fs.content_category = ?
AND fd.live_to < ? #live_from
AND live_to != '0000-00-00'