I've got a table where 2 of the columns are dates, Date1 and Date2.
One of the columns (Date1) can be null (0000-00-00). I want to query
the db so that I retrieve the next 3 dates, regardless of which column
they're in. I'm converting Date1 to a Unix Timestamp. I've got as fas
as the following code:

SELECT
Date1,
Unix_Timestamp(Date1) AS UnixTime,
Date2
FROM Table1
WHERE
(Date2 > SUBDATE(Now(), 1))
OR
(((Date1 = 0000-00-00 && (Date2 > SUBDATE(Now(), 1)))))
ORDER BY IF(UnixTime<>0, `UnixTime`, `Date2`), UnixTime"

(and then I know I'll need LIMIT 3 at the end...); however, the ORDER
BY still means that a date2 date that is before a Date1 date is not
returned in the correct order.

What I have envisaged I'd need to return is a combined column, that I
can then sort, but I'm not sure how to accomplish that. I had also
thought I might be able to use an if or case statement either in the
select or where clauses.

Any insight will be very grateful!

Chris

On 25-02-11 14:50, Chris W wrote:
SELECT Date1 as D From Table1
UNION
SELECT Date2 as D From Table1
ORDER BY D DESC
LIMIT 3

This should give the max 3 dates.....


--
Luuk

On Feb 25, 2:07*pm, Luuk <L... (AT) invalid (DOT) lan> wrote:
Thanks for the reply. I hadn't realised you could use a join
statement like that. It is actually more complex than I originally
stated - I do have more columns in the table, and I do want to be able
to print out both date columns from the next 3 dates. Your code seems
like a good starting point tho.

Chris

On Feb 25, 3:09*pm, Chris W <q... (AT) live (DOT) co.uk> wrote:
He didn't use a JOIN statement. JOIN statements are recognisable by
having the word "JOIN" in them (in the case fo explicit joins) or
commas separating input tables (in the case if implicit joins).

He used a UNION statement.

Captain Paralytic wrote:
Good point.

On Feb 25, 3:18*pm, Captain Paralytic <paul_laut... (AT) yahoo (DOT) com> wrote:
Sorry - my misunderstanding - thanks for pointing it out

On Feb 25, 3:09*pm, Chris W <q... (AT) live (DOT) co.uk> wrote:
I'm still trying to work this out.

The SQL is actually for an events list. Each event has a booking
deadline and the date it actually takes place. I've now got:

SELECT ID, Venue, EventName, Date1, Date2, Date1 as D From Table1
WHERE Date1 > SUBDATE(Now(),1)
UNION
SELECT ID, Venue, EventName, Date1, Date2, Date2 as D From Table1
WHERE Date2 > SUBDATE(Now(),1)
ORDER BY CombinedDate ASC;

However, this returns 2 rows for each event. Is there a way to only
return 1 row - so that, for example, in my final list of 3 events,
each 3 are different even if one of the events has a booking deadline
of tomorrow, an event date of the day after and the other 2 events
both have event dates after that?

Thanks
Chris

On 26-02-11 13:45, Chris wrote:
SELECT ID, Venue, EventName, Date1, Date2, Date1 as D
From Table1
INNER JOIN (
SELECT ID, Date1 as D
From Table1
WHERE Date1 > SUBDATE(Now(),1)
UNION
SELECT ID, Date2, Date2 as D
From Table1
WHERE Date2 > SUBDATE(Now(),1)
ORDER BY D DESC
LIMIT 3) X ON Table1.ID=X.ID
ORDER BY X.D

--
Luuk

On 26 Feb, 13:08, Luuk <L... (AT) invalid (DOT) lan> wrote:
Thanks for all the helpful suggestions. Unfortunately, Luuk's code
still returned 2 rows for each event (for which I take responsibility
for not explaining my problem / data well enough); however, taking a
different approach, I've found that the following works, and seems
fairly efficient:

SELECT EventID, Name, Venue, Date1, Date2,
CASE
WHEN Unix_Timestamp(Date1) > UNIX_TIMESTAMP()
THEN Date1 ELSE Date2
END AS 'D'
FROM Events
HAVING Unix_Timestamp(D) > UNIX_TIMESTAMP()
ORDER BY D ASC LIMIT 3;

Chris

On 02-03-11 13:47, Chris wrote:
Dont feel responable for that, it could als have been /me not reading
your problem correct

This will always to a full-table-scan (i mean, it will not use an index)
So, if tables becomes very large, your query might become very slow

But if you tested it, and it gives correct results....

--
Luuk