• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
LEFT JOIN for noobs

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:
prod_id  |  prod_price  | prod_sku  |  prod_id  |  prod_image  |  prod_id  |  prod_stock

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

Why don't you define the fields you want in a select-statement?

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"?

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

You can also use an alias. You can also add an alias:
->select('table1.prod_id prod_id')

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:
$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.

Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  

Users browsing this thread:
1 Guest(s)

  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2019 MyBB Group.