i created a div on my homepage to display the most recent items from four tables on my database, but i'm having problems with the structure of the query, i tried this for the model:
$sql = "(SELECT name,type, screenshot,url from music WHERE status = 1 )
UNION ALL (SELECT name,type, screenshot,url from lyrics WHERE status = 1 )
UNION ALL(SELECT name,type, screenshot,url from mixtapes WHERE status = 1 )
UNION ALL (SELECT name,type, screenshot, vurl from videos WHERE status = 1 ) ORDER BY 'date_added' desc LIMIT 8";
$query = $this->db->query($sql);
return $query->result();
this query retrieves only from the music table, and the limit keyword applies only to the items retrieved from the music table, but i want the query run as one query, so that the items are retrieved based on the latest items from the four tables and the limit keyword limits the results to eight items, please how do i construct the query, or is there any other way to do it? please help..