• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
join with multiple conditions

#1
[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

#2
[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();

#3
[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

#4
[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");

#5
[eluser]bkarat[/eluser]
Hi !

Thanks a lot. It works now.

Thanks again.

With regards,
Bala

#6
[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?

#7
[eluser]bretticus[/eluser]
I got rid of the parens and ordered my joins differently and it worked.

#8
[eluser]darkhouse[/eluser]
yeah you definitely didn't need any parenthesis in that statement.

#9
[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. Smile


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2020 MyBB Group.