select from multiple tables but ordering by a datetime field
- by Chris Mccabe
I have 3 tables that are unrelated (related that each contains data for a different social network). Each has a datetime field dated- I'm already grouping by hour as you can see below (this one below for linked_in)
SELECT count(*), date_format(dated, '%Y:%m:%d %H') as hour
FROM upd8r_linked_in_accts
WHERE CAST(dated AS DATE) = '".$start_date."'
GROUP BY hour
I would like to know how to do a total across all 3 networks- the tables for the three are
CREATE TABLE IF NOT EXISTS `upd8r_facebook_accts` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`owner_id` varchar(50) NOT NULL,
`user_id` int(11) NOT NULL,
`fb_id` bigint(30) NOT NULL,
`dated` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=80 ;
CREATE TABLE IF NOT EXISTS `upd8r_linked_in_accts` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`owner_id` varchar(50) NOT NULL,
`user_id` int(11) NOT NULL,
`linked_in` varchar(200) NOT NULL,
`oauth_secret` varchar(100) NOT NULL,
`first_count` int(11) NOT NULL,
`second_count` int(11) NOT NULL,
`dated` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=200 ;
CREATE TABLE IF NOT EXISTS `upd8r_twitter_accts` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`owner_id` varchar(50) NOT NULL,
`user_id` int(11) NOT NULL,
`twitter` varchar(200) NOT NULL,
`twitter_secret` varchar(100) NOT NULL,
`dated` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;
something like this ?
(SELECT count(*), date_format(dated, '%Y:%m:%d %H') as hour
FROM upd8r_linked_in_accts
WHERE CAST(dated AS DATE) = '".$start_date."')
UNION ALL
(SELECT count(*), date_format(dated, '%Y:%m:%d %H') as hour
FROM upd8r_facebook_accts
WHERE CAST(dated AS DATE) = '".$start_date."')
UNION ALL
(SELECT count(*), date_format(dated, '%Y:%m:%d %H') as hour
FROM upd8r_twitter_accts
WHERE CAST(dated AS DATE) = '".$start_date."')
UNION ALL
GROUP BY hour
