CodeIgniter Forums

Full Version: Query from multiple tables
You're currently viewing a stripped down version of our content. View the full version with proper formatting.

El Forum

[eluser]Unknown[/eluser]
Hi All,

I am a beginner in codeigniter. Could you please help me.

I want to return result from two tables.

The table structures are as given below.

Code:
1) user :   id     int(11), username varchar(16),status tinyint(1)
2) transaction : id int(11), tranType varchar(20),status tinyint(1) ,userid int(11)

Here relation between user and transaction table is one to many and I want to show the max record from transaction table grouping by user.id.

for example

user table has the following records

Quote:ID userName status
1 John 1
2 James 1

transaction table has the following records

Quote:Id TranType Status userID
1 signup 1 1
2 payment 1 1
3 approved 1 1


so here it should return the result as follow
Quote:user.ID userName,TranType userStatus Tran Status
1 John approved 1 1

How can I write a query for codeigniter joining the both tables and return the result with specific columns.
Code:
[user.Id,user.userName,transaction.tranType,user.status,transaction.status]

Many appreciation for you quick reply

thanks

El Forum

[eluser]dUspan[/eluser]
$query = "SELECT * FROM tbl1 LEFT JOIN tbl2 ".
"ON tbl1.ID = tbl2.ID WHERE tbl1.id = 1"

$this->db->query($query);

try this.im not sure.but i hope it helps.


Big Grin