Referencing or linking mysql tables

[eluser]Unknown[/eluser]
I am not great with MySQL or databases, and to make things even trickier for me, someone has handed me a site over to modify that was done with Codeigniter, so I'm looking for a little help hopefully. I will try to explain this as best possible.

Basically I have page with an array that was already created and it displays all the information from a single database table called "transactions", into an html table showing all rows... for example:
Load Number | Date | Producer Number | Field 1 | Field 2 | Field 3...

Everything works great, the only problem is that in the database table, the Producer Number field (id_producer) is just a random number that is assigned incrementally to each producer. The number I actually want displayed in that row is their license number, which is in another table called "producer". (See example image - http://i47.tinypic.com/2igj1wn.jpg) The tables are linked... so I am just trying to figure out if there is an easy way to display the license number (104477 in that example) instead of the Producer ID (485).



The code that displays the table looks like this and seems to grab only from the "transactions" table:

Code:

<td>&lt;?= $row['load_number'] ?&gt;</td>
<td>&lt;?= date('M d, Y', strtotime($row['pick_up_date'])) ?&gt;</td>
<td class="right">&lt;?= [b]$row['id_producer'][/b] ?&gt;</td>

...so I'd like somehow that third line to have it grab 'license_number' from the "producer" table instead. Since they're linked I figure there must be an easy way to cross-reference those?

I hope that makes sense...

[eluser]bgreene[/eluser]
select t.loadnumber,t.date,p.license_number,t.field1,t.etc from transactions t left join producers p on p.id=t.producerid. use left join just in case a producer goes missing

[eluser]sanir[/eluser]
Use join for that.

Code:

$this->db->select('*');
$this->db->from('blogs');
$this->db->join('comments', 'comments.id = blogs.id');

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

// Produces:
// SELECT * FROM blogs
// JOIN comments ON comments.id = blogs.id

Thanks,
Nasir Ranta