+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: LEFT JOIN for noobs (/showthread.php?tid=19669)
LEFT JOIN for noobs - El Forum - 06-15-2009
[eluser]slowgary[/eluser]
Hi guys,
I have a query that left joins a few tables on the primary id, but when I get the results back the id field is duplicated (because it's in each table).
So my results look like this:
Code:
prod_id | prod_price | prod_sku | prod_id | prod_image | prod_id | prod_stockThis works fine when all fields have data in them, but when the last table returns NULL as the prod_id, my code gets that one, which breaks everything.
Is there a way to only get back one prod_id field? Preferably from the first table. Thanks.
LEFT JOIN for noobs - El Forum - 06-15-2009
[eluser]sl3dg3hamm3r[/eluser]
Why don't you define the fields you want in a select-statement?
LEFT JOIN for noobs - El Forum - 06-15-2009
[eluser]slowgary[/eluser]
Is that the only way? I was going to try that but the only fields I DON'T want are the multiple IDs. That means I'd have to specify every field and there are a bunch of them. Also, if I add new fields to the table I'd also have to change the code. Is there a way to invert a selection, or just say "! table2.prod_id"?
LEFT JOIN for noobs - El Forum - 06-15-2009
[eluser]slowgary[/eluser]
It was actually less fields than I thought because the first table contained most of them, and I only needed 1 field from each of the other tables, so I have something like:
Code:
->select('table1.*, table2.field, table3.field')That works like a charm. Thanks sl3dg3.
Would be nice to have a SELECT INVERT though for other cases.
LEFT JOIN for noobs - El Forum - 06-15-2009
[eluser]TheFuzzy0ne[/eluser]
You can also use an alias. You can also add an alias:
Code:
->select('table1.prod_id prod_id')LEFT JOIN for noobs - El Forum - 06-15-2009
[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']