join with multiple conditions - Printable Version +- CodeIgniter Forums (https://forum.codeigniter.com) +-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20) +--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23) +--- Thread: join with multiple conditions (/showthread.php?tid=16120) |
join with multiple conditions - El Forum - 02-24-2009 [eluser]bkarat[/eluser] Hi ! I am new to CodeIgniter. The following query style is working fine in CodeIgniter: $query=$this->db->query("Select b.INVCODE, b.INVNAME, b.LESSTYPE, a.PROPOSEDAMT, a.INVAMT from STD b left outer join EMP a on (b.INVCODE=a.INVCODE) and (a.FINSHORT='$finyear') and (a.EMPCODE='$user')"); I am not getting the exact syntax to put this in Active Record style. When I use the last two conditions in a where clause, the result is not correct. Thanks in advance. With regards, Bala join with multiple conditions - El Forum - 02-24-2009 [eluser]darkhouse[/eluser] That's because you don't have a where clause to begin with. Just put "(b.INVCODE=a.INVCODE) and (a.FINSHORT=’$finyear’) and (a.EMPCODE=’$user’)" in the second parameter of your $this->db->join(); join with multiple conditions - El Forum - 02-25-2009 [eluser]bkarat[/eluser] Thanks for yr reply. I am getting an error on this line -> "Parse error: syntax error, unexpected T_VARIABLE in ....." My join looks like this now: $this->db->join('EMP a','(b.INVCODE=a.INVCODE) and (a.FINSHORT='$finyear') and (a.EMPCODE='$user')','left outer'); With regards, Bala join with multiple conditions - El Forum - 02-25-2009 [eluser]darkhouse[/eluser] Looks like you need to escape your quotes, or just use double quotes to wrap your statements. Try this. Code: $this->db->join("EMP a", "(b.INVCODE=a.INVCODE) and (a.FINSHORT='$finyear') and (a.EMPCODE='$user')", "left outer"); join with multiple conditions - El Forum - 02-25-2009 [eluser]bkarat[/eluser] Hi ! Thanks a lot. It works now. Thanks again. With regards, Bala join with multiple conditions - El Forum - 03-25-2009 [eluser]bretticus[/eluser] Funny, I am using CI 1.7.1 and when I use the following line for multiple conditions on my join: Code: $this->db->join('ProductPhotos', '(Products.ProductID = ProductPhotos.ProductID) AND (Photos.Type = \'small\')', 'left'); I get an error because it produces this: Code: LEFT JOIN `ProductPhotos` ON `Products`.`ProductID` = `ProductPhotos`.`ProductID)` AND (Photos.Type = 'small') It is automatically whacking off the first parenthesis. Anybody have a similar problem? join with multiple conditions - El Forum - 03-25-2009 [eluser]bretticus[/eluser] I got rid of the parens and ordered my joins differently and it worked. join with multiple conditions - El Forum - 03-25-2009 [eluser]darkhouse[/eluser] yeah you definitely didn't need any parenthesis in that statement. join with multiple conditions - El Forum - 03-25-2009 [eluser]bretticus[/eluser] [quote author="darkhouse" date="1238051365"]yeah you definitely didn't need any parenthesis in that statement.[/quote] Right, I didn't pay attention the very first time (no parens) to the real database error and took myself on a wild goose hunt. |