MySQL Forums
Forum List  »  Newbie

Re: returning the closest date
Posted by: David Fells
Date: April 07, 2005 09:30PM

Ack, I made some ugly typos there didn't I? My apologies. As for the "train wreck" comment, the design just looks like you're asking for erroneous data, and the intent of the purchaset table is not especially clear. I the stock table related to it by the pid, but that could be wrong - either way, if you want your table structure to be _crystal_ clear, simply post your create table statements along with your question ;)

Anyway I corrected my typos. This should give you what you are asking for.

SELECT p1.pid, c1.colour,
(SELECT p2.seadate FROM purchaseset AS p2 WHERE p2.seadate > p1.seadate AND p2.pending = 1 AND NOT EXISTS(SELECT * FROM purchaseset AS p3 WHERE p3.seadate BETWEEN p1.seadate AND p2.seadate),
(SELECT p4.airdate FROM purchaseset AS p4 WHERE p4.seadate > p1.seadate AND p4.pending = 1 AND NOT EXISTS(SELECT * FROM purchaseset AS p5 WHERE p5.airdate BETWEEN p1.airdate AND p4.airdate)
FROM purchaset AS p1
INNER JOIN stock AS s1
ON s1.pid = p1.pid
INNER JOIN prodcolour AS c1
ON c1.colourid = s1.colourid
WHERE p1.pending=1
AND p1.airdate >=0
AND p1.seadate >=0

Options: ReplyQuote


Subject
Written By
Posted
t c
April 06, 2005 09:23PM
t c
April 07, 2005 02:30PM
Re: returning the closest date
April 07, 2005 09:30PM
t c
April 07, 2005 11:05PM
t c
April 07, 2005 04:28PM
t c
April 07, 2005 04:29PM
t c
April 08, 2005 04:23PM


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.