Re: How compare fields in one query from different tables

No, it's not work. I guess I figured out how I can solve my problem. With this sql queries I select what I need:

CREATE TEMPORARY TABLE tmp_details(
id int( 10 ) NOT NULL ,
comment_id int( 5 ) NOT NULL ,
incident_id int( 11 ) NOT NULL ,
PRIMARY KEY ( id )
);

INSERT INTO tmp_details
SELECT Detail.id, MAX( Detail.comment_id ) , Detail.incident_id
FROM details Detail
GROUP BY Detail.incident_id;

SELECT Incident.id, Incident.exp_date, Detail2.comment_date
FROM tmp_details Detail1
INNER JOIN details Detail2 ON Detail1.incident_id = Detail2.incident_id
AND Detail1.comment_id = Detail2.comment_id
INNER JOIN incidents Incident ON Incident.id = Detail1.incident_id
AND Incident.exp_date > Detail2.comment_date
AND Detail2.comment_date != "0000-00-00 00:00:00"

And now there is another problem. How to use temporary tables in Cake? Furthermore I need to paginate results that I will get from last query.

--
Our newest site for the community: CakePHP Video Tutorials http://tv.cakephp.org
Check out the new CakePHP Questions site http://ask.cakephp.org and help others with their CakePHP related questions.
 
 
To unsubscribe from this group, send email to
cake-php+unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php