Welcome Guest, Not a member yet? Register   Sign In
Select from multiple tables most recent
#1

[eluser]heylarson[/eluser]
I am attempting to write a query that allows me to select events from one table and news posts from another table and display them in order of their posting time/event start time; limit 10 all together.

Something like:

Code:
$this->db->select('events.start_time, events.title, news.post_time, news.title');
$this->db->from('events', 'news');
$this->db->order_by('events.start_time', 'desc');
$this->db->order_by('news.post_time', 'desc');
$this->db->limit(10);
$query = $this->db->get();

If anyone knows how to make this work, I would be extremely appreciative!
#2

[eluser]mddd[/eluser]
That's what the UNION keyword is for. Mysql code:
Code:
(SELECT start_time AS t, title FROM events) UNION (SELECT post_time, title FROM news) ORDER BY t DESC LIMIT 10

I don't think CI supports UNION so you'll have to do the query using $this->db->query( $my_query );

See the Mysql page on UNION.




Theme © iAndrew 2016 - Forum software by © MyBB