[eluser]Zorancho[/eluser]
If for example you have 3 tables that have prod_id field like this:
table1: prod_id, prod_image, prod_name;
table2: prod_id, prod_image, prod_name;
table3: prod_id, prod_image, prod_name;
And you want to select all, but only the prod_id from table1, you do it this way:
Code:
$sql = "SELECT tone.*,
ttwo.prod_image AS image_two, ttwo.prod_name AS prod_two,
tthree.prod_image AS image_three, tthree.prod_name AS prod_three
FROM table1 tone
LEFT JOIN table2 ttwo ON tone.prod_id = ttwo.prod_id
LEFT JOIN table3 tthree ON tone.prod_id = tthree.prod_id
WHERE some condition";
//This will select all from table1 in any way and it will select prod_image and prod_name //from the other two tables as image_two, prod_two, image_three, prod_three but only if //prod_id is same with the one in table1.
//Then you query the sql:
$q = $this->db->query($sql);
if($q->num_rows() > 0)
{
foreach($q->result_array() as $row)
{
$data[] = $row; //Initialise the $data before as $data = array();
}
}
//Then you get number of rows and each row has this:
$row['prod_id'], $row['prod_image'], $row['prod_name']
//If the prod_id from table2 is same as the one in table1 it will return additionally:
$row['image_two'], $row['prod_two']
//If the prod_id from table3 is same as the one in table1 it will return additionally:
$row['image_three'], $row['prod_three']
Have a look at this:
http://www.w3schools.com/Sql/sql_join_left.asp and if you cannot manage, then send me PM and i will help you build your queries any way you want.
