sql question

ChicoMendez

hi,

i have two comment tables (lets say one for blogs and one for photos) and i want to be able to display one module with last five comments ordered by date.

is there anyway to do this with one sql query.


here are the tables if thats any help :
Thanks




CREATE TABLE `jos_blog_comment` (
`id` int(11) NOT NULL auto_increment,
`user_id` int(11) NOT NULL,
`comment_name` varchar(100) NOT NULL,
`comment_email` varchar(100) NOT NULL,
`post_id` int(11) NOT NULL COMMENT 'Blog_post_id',
`comment_title` varchar(150) NOT NULL,
`comment_desc` text NOT NULL,
`comment_date` datetime NOT NULL,
`comment_update` datetime default NULL,
`comment_ip` varchar(15) NOT NULL,
`comment_hit` int(11) default NULL,
`checked_out` mediumint(9) NOT NULL,
`checked_out_time` datetime NOT NULL,
`published` tinyint(1) default '1',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;



CREATE TABLE `jos_comment` (
`id` int(11) NOT NULL auto_increment,
`comment_desc` text NOT NULL,
`comment_date` datetime NOT NULL,
`comment_ip` varchar(15) NOT NULL,
`comment_name` varchar(50) default NULL,
`comment_email` varchar(50) NOT NULL,
`published` tinyint(1) default '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=23 ;

MOH

This should do it

select <fields you want> from jos_blog_comment
union
select <fields you want> from jos_comment
order by comment_date desc

You'll need to select the same number of fields from each table, and they should be of the same type - e.g if you select varchar, int, varchar, varchar, int from the first table you need to select varchar, int, varchar, varchar, int from the second table.

Shouldn't be a problem since all your jos_comment fields appear in job_blog_comment, so you could select all of them.

ChicoMendez

hi

thanks for that however have little issue :


No valid database connection Unknown column 'comment_date' in 'order clause' SQL=SELECT comment_name,comment_desc FROM jos_comment UNION select comment_name,comment_desc from jos_blog_comment WHERE published=1 ORDER BY comment_date DESC


my query is


SELECT comment_name,comment_desc FROM jos_comment UNION
select comment_name,comment_desc from jos_blog_comment
WHERE published=1 ORDER BY comment_date DESC


any idea.

cheers

none

Try adding comment_date to both SELECTs.