CODEWITHSUNDEEP
mysqlsql
Vince
Vince

Reputation: 1535

Need Help formulating an SQL Query

I'm using MySql 5.5. Let's start by looking at a sample of my table: 

pickupid    pickuplocation      Date1       Date2       Date3       Date4
1           Collingwood         1328079600  1330585200  1333260000  1335852000
2           Varsity             1328079600  1330585200  1333260000  1335852000
3           Canmore             1328079600  1330585200  1333260000  1335852000
4           Westbrook           1328079600  1330585200  1333260000  1335852000

I need a query which will select only those dates which are in the future. If a date is in the past the query must skip over it. I have tried using Select with Have, Or, Where & AND. My queries fail because if one of the dates happens to be in the past then the whole query comes back with zero results. Yes, I know that if I orientated my table around the other way ie with locations in the columns and dates in the rows it could make it easier - I've tried that, but I run into other problems as my HTML report has to accommodate over 50 locations, but only 4 dates.

Many Many Thanks for All your Help !!

Upvotes: 1

Views: 90

Answers (1)

keithhatfield
keithhatfield

Reputation: 3273

Since there's not a lot of detail about what you're trying to get, I'm just taking a guess, but, the below query will return all results where one of the dates is in the future, only the date columns with dates in the future will have data in them, the others will be set to NULL.

You can then exclude the null dates in your processing ...

Is this what you're going for?

SELECT
    pickupid,
    pickuplocation,
    IF(Date1 > NOW(),Date1,NULL) AS Date1,
    IF(Date2 > NOW(),Date2,NULL) AS Date2,
    IF(Date3 > NOW(),Date3,NULL) AS Date3,
    IF(Date4 > NOW(),Date4,NULL) AS Date4
FROM
    locations_table
WHERE
    Date1 > NOW() 
    OR Date2 > NOW()
    OR Date3 > NOW()
    OR Date4 > NOW()

Upvotes: 2

Related Questions