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
