CODEWITHSUNDEEP
phpmysqlcodeigniter
black_belt
black_belt

Reputation: 6799

Finding out related data from tables

I have three tables in my mysql database which looks like following: enter image description here

EDIT

The table "members" holds the name and code of some members . Now the table "Boss" contains the name of the member who has been selected as a Boss for an event. Each boss has at least one subordinate (please check the table subordinates).

Now what I am trying to do is find out the names and their points that Bon Jovi (pls check the table table:members) is associated with. When finding out there is a condition... if the "condition_type" from table:boss is 1 then the values from subordinates would be in the right side and if the "condition_type" from table:boss is 0 then the values from subordinates would be in the left side.

Now what I am trying to find out looks like following.

enter image description here

Upvotes: 1

Views: 115

Answers (1)

Muhammad Raheel
Muhammad Raheel

Reputation: 19882

OK i have found a solution for you i am posting

$condition  = 1;// Condition will be provided by you for selection. Could be zero  
$data   =   array(
        'members.name',
        'subordinates.points',
        'subordinates.event'
);

$this->db->select($data);
$this->db->join('members','members.code = subordinates.member_code','left');
$this->db->join('boss','boss.event = subordinates.event','left');
$this->db->where('boss.condition_type',$condition);
$this->db->where('boss.event    =   subordinates.event');
$this->db->get('subordinates');

Also i have event in the selection so that you recognize the difference of events the query will bring. Kindly test and see if it works ok for you. There might be a little difference in the column names. A good approach is to use like this for easy and understandable relationship between tables.

Table1 :  members
Columns : id , code , name
Table2 :  boss
Columns : id , event , member_code , condition_type
Table3 :  subordinates
Columns : id , boss_event , member_code , points

Upvotes: 2

Related Questions