how to get data from three tables by a join statement in mysql

In my application i have three tables, res开发者_C百科ervation, patient and sub_unit, i need to take the patient_id from reservation table and query the patient table for patient data,same time i need to take the sub_unit_id from the reservation table and query the sub_unit name from the sub_unit table... i need to put all this data in to an one array in the sequence like patient_id, sub_unit_name, patient_name, address and pass it to the Codeigniter table class to draw a table.

How can I query three tables in the same time to query out this data? can you guys help me out?

Using code igniter syntax it can be done as follows -

$this->db->select('r.patient_id, s.sub_unit_name, p.patient_name, p.address');
$this->db->from('reservation r');
$this->db->join('patient p', 'p.id = r.patient_id');
$this->db->join('sub_unit s', 's.id = r.sub_unit_id');
$query = $this->db->get();


You can check your formed query by -
echo $this->db->_compile_select();exit;

Select r.patient_id, s.sub_unit_name, p.patient_name, p.address 
from reservation r, sub_unit s, patient p 
where r.patient_id = p.patient_id and r.sub_unit_id = s.sub_unit_id 

The join syntax is very straightforward in SQL. You are probably looking for something like this:

SELECT   reservation.patient_id, 
         sub_unit.sub_unit_name, 
         patient.patient_name, 
         patient.address
FROM     reservation
JOIN     patient ON (patient.id = reservation.patient_id)
JOIN     sub_unit ON (sub_unit.id = reservation.sub_unit_id);

In MySQL, the default join is an Inner Join, which I think is what you're looking for. You may also want to look into Outer Joins which are also very useful.

it worked guys , i did it like this using Codeigniter active records ,hope you guys can use it too

    function get_data(){
    $sql = 'SELECT * FROM visit,patient,sub_unit WHERE visit.patient_patient_id = patient.patient_id AND visit.sub_unit_sub_unit_id = sub_unit.sub_unit_id';

    $this->db->order_by("reference_number", "desc");
    $query = $this->db->query($sql);
    return $query;
}

thanx for all your support!