MYSQL select records on Active date

Question

Hi I have a query i'm stuck on where i would like to select the records that are on the active date. The following is my code:

SELECT DISTINCT
    Table1.Field1,  
    Table2.Field1,  
    Table3.Field1,
FROM Table1
           inner join Table2 on Table1.Field1 = Table2.Field1
           inner join Table3 on Table1.Field1 = Table3.Field1   
WHERE Table1.Field1 IN (SELECT Table4.Field1 FROM Table4)

Table1.Field1 is my ID field that has multiple rows due to the date field, Table3.Field1 is the date field. I only want the row with the active date so for example here is one record with multiple rows:

Table1.Field1  Table3.Field1

12345           2013-09-11   
12345           2013-12-11  
12345           2014-03-11  
12345           2014-06-11  
12345           2014-09-17

The row i want from this record is: 12345 2014-06-11, therefore i do not want to pull any row in the future but i want to pull the latest active row from the past if that makes sense. Thanks in Advance!

Why do you not have a WHERE statement for Field4 then? – Devon Bessemer Jun 24 '14 at 17:11 — Devon Bessemer, Jun 24 '14 at 17:11

score 1 · Answer 1 · answered Jun 24 '14 at 17:11

1

I prefer effective and end dates for this type of data structure. But you can still get what you want:

select t1.*
from table1 t1
where t1.date = (select max(t11.date)
                 from table1 t11
                 where t11.field1 = t1.field1 and
                       t11.date <= now()
                );

That is, pull the record for the id with the maximum date \less than or equal to the current date.

answered Jun 24 '14 at 17:11

Gordon Linoff

1,242,037
58
646
786

Thanks for your quick response, however i'm not quite understanding the answer and also i have had to re-edit my question due to my date field being in another table so have now included the joins. I looked at the post which suggests my question has already been answered but from what i can tell that is getting the last date row, as mentioned i do not wish to obtain the last date as it could be in the future. I only want records that has no date in the future and to get the last but most current date in the past. Thanks – Jimmy Jun 24 '14 at 18:17
@Youcef . . . So replace `table1` with the appropriate joins, both in the outer query and in the subquery. – Gordon Linoff Jun 24 '14 at 23:27

MYSQL select records on Active date

1 Answers1