1

I want to select data from 4 tables using the Codeigniter framework. The 4 tables have a similar column structure. I want to get the table data corresponding to certain year and month.

This is my table structure:

Table t1:

accid     uid      month    year        ccbalance
--------------------------------------------------------
101         19       May      1996        4545
-----------------------------------------------------
101         19       sept      1998         1500
--------------------------------------------------------

Table t2:

accid     uid      month      year        insbalance
--------------------------------------------------------
102         19       May       1995         2059
-----------------------------------------------------
102         19       july       1998         2500
--------------------------------------------------------

Table t3:

accid     uid      month    year        ccbalance
--------------------------------------------------------
109         19       June      1999         10000
-----------------------------------------------------
109         19       Aug       1990        1500
--------------------------------------------------------

Table t4:

accid     uid      month    year        ccbalance
--------------------------------------------------------
105         19       Aug      1995         10000
-----------------------------------------------------
105        19       May       1995         3333
--------------------------------------------------------

If I select May 1995, I want to get this result:

accid     uid      month    year        ccbalance
--------------------------------------------------------
105         19       May     1995         3333

102         19       May     1995         2059
Nathaniel Ford
  • 20,545
  • 20
  • 91
  • 102
anju
  • 101
  • 1
  • 10
  • In SQL, this is something like `(t1 UNION t2 UNION t3 UNION t4) WHERE month="May" AND year=1995` maybe this helps you finding the codeigniter implementation. Also helpful: http://stackoverflow.com/questions/2040655/union-query-with-codeigniters-active-record-pattern – Daniel Alder Sep 08 '15 at 11:58
  • It looks like you don't want a "join" (gather different info about the same item) but rather an "union" (gather info about items from different sources) – Aaron Sep 08 '15 at 11:59

1 Answers1

0

there is no built in union function in Codeigniter 2.0 and 3.0

you can create your own sql query and execute it like this:

$sql="(SELECT * from t1 where month='May' AND year=1995)
       UNION
      (SELECT * from t2 where month='May' AND year=1995)";
$query = $this->db->query($sql);    
return $query->result();

more information on mysql union syntax you find here (official documentation)

Vickel
  • 7,879
  • 6
  • 35
  • 56