Assuming that you *don't* want ALL purchases made prior to the range, but
only those of subjects not purchased recently, the following query appears
to work:
-- First gather info about recent purchases
Create TEMPORARY Table recent
SELECT B.id, B.date_purch, S.subj
FROM subj as S
INNER JOIN book as B
ON S.book_id = B.id
-- Last 6 months:
WHERE B.date_purch >= DATE_ADD(CURRENT_DATE(), INTERVAL -6 MONTH);
-- Then do an outer join with ALL purchases
SELECT B.id, B.date_purch, S.subj
FROM subj as S
INNER JOIN book as B
ON S.book_id = B.id
LEFT OUTER JOIN recent as R
ON R.subj = S.subj
-- but list only those with no recent purchases:
WHERE R.id IS NULL;
If you only want the subjects returned (and once each), remove B.id and
B.date_purch from the (2nd) SELECT list, and include a DISTINCT.
As a MySQL newbie, I couldn't get it to work as a single statement which
"logically" (AFAIK) should have returned the same data on 4.0 (returned no
result on 4.0, gave error on 3.23):
SELECT B.id, B.date_purch, S.subj
FROM subj as S
INNER JOIN book as B
ON S.book_id = B.id
LEFT OUTER JOIN
(book as B2
INNER JOIN subj as S2
ON S2.book_id = B2.id
AND B2.date_purch >= DATE_ADD(CURRENT_DATE(), INTERVAL -6 MONTH)
)
ON S2.subj = S.subj
WHERE B2.id IS NULL
Is there a restriction, quirk, or bug in MySQL that prevents parenthesised
joins from working properly?
HTH,
Tore.
----- Original Message -----
From: "Jesse Sheidlower" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Monday, February 24, 2003 8:09 PM
Subject: Select based on related date
>
> I'm having trouble with a query that I thought would be pretty
> straightforward. To simplify, I have a database of books that
> has, say, two tables:
>
> CREATE TABLE book (
> id INT,
> date_purch DATE
> )
>
> CREATE TABLE subj (
> book_id INT,
> subj TEXT
> )
>
> Each book can have any number of subjects, and each book has
> at least one subject.
>
> I'd like to get all subjects that are in the subject table
> that are _not_ represented in a particular date range. For
> example, if I have bought books with subjects 'Computing',
> 'Cooking', 'Baseball', and 'Fiction', but in the last six
> months I have only bought Fiction and Baseball, I'd like a
> query that will give me Computing and Cooking.
>
> I played around with a few LEFT JOINS but I still seem to be
> missing something.
>
> Thanks.
>
> Jesse Sheidlower
>
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
