MySQL Forums
Forum List  »  Newbie

Re: Problem with advance query
Posted by: Peter Brawley
Date: March 18, 2009 03:52PM

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 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
  SELECT DISTINCT file_number
  FROM tbl
  WHERE bf_date >= CURDATE()
) AS b USING (file_number)
WHERE b.file_number IS NULL;


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

Options: ReplyQuote

Written By
March 18, 2009 01:45PM
Re: Problem with advance query
March 18, 2009 03:52PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.