Query from multiple tables

[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

ID userName status
1 John 1
2 James 1

transaction table has the following records

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

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

[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.