Regarding Conditional joins in MySql

[mohananu]

hi,

please help me out....


I have 2 table, Say T1 and T2
Above 2 table have a datatime field in it.
I want to left join this two table such that
if(T1.datatime field > T2.datatime field)
i want to populate the join records with the fields from T2
else
i want to populate the join records with fields from T2 as Null



please tell me ...thanks in advance

[Atli]

Hey.

Sounds like a straight-up LEFT JOIN to me.
Try something like:
[code=sql]SELECT `t2`.*
FROM `t1`
LEFT JOIN `t2`
ON `t1`.`id` = t2.`id`
AND `t1`.`datetime` > t2.`datetime`[/code]
That should give you a list containing a row for each row in t1, but only with the data from t2. Rows from t1 that don't have a corresponding row in t2, or where the datetime field in t2 is less than the datetime field in t1, return all the columns populated with NULL.

If you want each row in t2 joined with each row in t1, and only be filtered by the datetime check, just remove the ID check and make the datetime check the only ON clause for the LEFT JOIN.

Please note, however, that I haven't slept in like 24 hours so you may want to test that before you use it :)