CodeIgniter Forums

Full Version: LEFT JOIN to a subquery using CI
You're currently viewing a stripped down version of our content. View the full version with proper formatting.
Hi all,

I'm trying to do a left join to a sub-query. When I code a simple LEFT JOIN like...

         //LEFT JOIN mytable ON mytable.myfield=maintable.myfield


... everything works fine.

I need to do the following (equivalent SQL)...

LEFT JOIN(select * from othertable where = 100) AS oth ON

Codeigniter seems to do two things. It strips out the brackets around the subquery, and also produces SQL syntax as follows...

JOIN AS oth ON USING (left)

This is run on a Mysql database and it fails for 'You have an error in your SQL syntax'

I've tried various permutations of this but the result is the same. My sub-query will always be the same so I can use a string for it. Is there a way that I can inject the sub query string into the middle of the query to achieve a LEFT JOIN on a sub query?