Re: Problem with advance query

[Peter Brawley]

A NOT EXISTS() query would pretty much write itself, but it would perform like a somnolent pig. For examples (of NOT EXISTS, not of somnolent pigs), search http://www.artfulsoftware.com/infotree/queries.php for [NOT] EXISTS.

Abstractly your query asks for "all X which have no Y which in turn meets some condition". The most efficient way to retrieve it, usually, is called an exclusion join:

SELECT ... FROM X LEFT JOIN Y ON some_condition WHERE Y.some_key IS NULL

A simple way to build such a query is inside out: (i) assemble a query that retrieves on the condition, then (i) build an outer query that retrieves the desired rows and left join that to :

1. Build the query for file_numbers having bf_dates >= curdate():

SELECT DISTINCT file_number FROM tbl WHERE bf_date >= CRUDATE()

2. Left join to that from a list of file_numbers:

SELECT a.file_number
FROM tbl AS a
LEFT JOIN (
  SELECT DISTINCT file_number
  FROM tbl
  WHERE bf_date >= CURDATE()
) AS b USING (file_number)
WHERE b.file_number IS NULL;

PB
http://www.artfulsoftware.com



Edited 2 time(s). Last edit at 03/18/2009 03:54PM by Peter Brawley.