MySQL query

Anon

Hi,

I have a table with 2 date columns. I want to select all dates (rows) from column date1 that match a certain month and year. This is ok.

But I also want to select all rows where the month and year of column date2 match column date2 of the rows that where selected on column date1. If that makes sense!?

Is this possible in one mysql query or is there no other solution then first getting the matches on date1 and afterwards seperate querys for matches on column date1?

Thanks,

Raoul

Anon

Something like:

SELECT * FROM yourtable WHERE
(MONTH(date1)=.. AND YEAR(date1)=...)
OR (MONTH(date1)=MONTH(date2) AND YEAR(date1)=YEAR(date2))

No?

Anon

All you need is a LEFT JOIN of the table to itself. Use an alias so you can pretend they are two different tables:

select *
from mytable t1
left join mytable t2
on t1.date2 = t2.date2
and t1.id != t2.id
where t1.date1 = ... whatever you already have

Anon

I guess this will not work. I need to return the matches on date1 where date1 is equal to the first match on date2.

Raoul

Anon

heavy stuff for a MySQL newbie!

But I get the idea:

select *
from mytable t1
left join mytable t2
on t1.date2 = t2.date2
and t1.id != t2.id
where MONTH(t1.date1) = '$month' and YEAR(t.date1) = '$year'

this will do it?

Thanks,

Raoul

Anon

Looks like it should work.

Decide if you want the t1.id != t2.id, it might be better without it depending on whether you want to join the t1 row with the same row as t2.