• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Help with join tables

#1
[eluser]Unknown[/eluser]
Hello !,

I need help with query. I have two tables:

table: ron
-----------------
|id | label |
|-----------------|
| 1 | one |
|-----------------|
| 2 | two |
|-----------------|
| 3 | three |
|-----------------|
| 4 | four |
-----------------

table: ron_data
----------------------------------------
| id | level1 | level2 | level3 | level4 |
|----------------------------------------|
| 1 | id1 | id2 | id3 | id4 |
|----------------------------------------|
| 2 | id3 | id4 | id1 | id2 |
|----------------------------------------|
| 3 | id1 | id4 | id3 | id2 |
|----------------------------------------|
| 4 | id4 | id2 | id3 | id1 |
----------------------------------------

I want to join these two tables (every value from ron_data in field level1, level2, level3, level4 is id from table ron.
i want result:
----------------------------------------
| id | level1 | level2 | level3 | level4 |
|----------------------------------------|
| 1 | one | two | three | four |
----------------------------------------


I am using statement join:

$this->db->where('ron_data.level1', $segm);
if($segm1<>''){$this->db->where('ron_data.level2', $segm1);}
if($segm2<>''){$this->db->where('ron_data.level3', $segm2);}
if($segm3<>''){$this->db->where('ron_data.level4', $segm3);}
$this->db->select('ron_data.*, ron.id AS label_id, ron.label AS label');
$this->db->join('ron', 'ron_data.level1 = ron.id ');
$query=$this->db->get('ron_data');

With this code i get only first column level1 joined.

Please help

#2
[eluser]Jan_1[/eluser]
why that if-clause?
what is your searchkey '$segm1'? label or id of ron?

#3
[eluser]danmontgomery[/eluser]
Code:
$this->db->join(‘ron’, ‘ron_data.level1 = ron.id ‘);

Because you're only joining on level1...

Code:
$this->db->select('ron_data.id, l1.label AS level1, l2.label AS level2, l3.label AS level3, l4.label AS level4', FALSE)
    ->join('ron AS l1', 'l1.id = ron_data.level1')
    ->join('ron AS l2', 'l2.id = ron_data.level2')
    ->join('ron AS l3', 'l3.id = ron_data.level3')
    ->join('ron AS l4', 'l4.id = ron_data.level4')
    ->where('ron_data.level1', $segm)
    ->get('ron_data');


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


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