CODEWITHSUNDEEP
phpmysqlcodeigniter
Yasitha
Yasitha

Reputation: 911

mysql query for select from two mysql tables

I'm using codeigniter.

We have two mysql tables.

What I'm doing is taking all the details in the first table and feeding it to an html page to let all users to view it. But now I want to add some details from a second table related to a column in first table.

I mean, I have a column in first table call "Pending_on" in that column I have inserted some branch names and in the second table I have inserted the contact details of those departments.

So, if we think in the first table fifth row pending_on column has the value "HR" then I want to display the telephone number of the HR department.

So, how can I write a query for this?

Upvotes: 0

Views: 251

Answers (3)

Sumeet Sadarangani
Sumeet Sadarangani

Reputation: 76

For your case, here is an example:

There are 2 tables named entry and subscription.

      entry
        field1    publisherId
        field2    entry

      subscription
        field1   myId
        field2   friendId

You can use a JOIN. In plain SQL:

SELECT e.publisherId, e.entry
FROM entry e
JOIN subscription s
ON s.friendId = e.publisherId
WHERE s.myId = 1234

Upvotes: 2

yuvaraj bathrabagu
yuvaraj bathrabagu

Reputation: 226

Let's take your example. You have a column name pending_on in your first table, and in your second table you have your department_name column. Find the below code for joining those two tables,

$this->db->select('firsttable.*,secondtable.contact_details');
$this->db->from('firsttable');
$this->db->join('secondtable', 'firsttable.pending_on = secondtable.department_name','left');
$query = $this->db->get();

So, The department_name in second table and pending_on in first table should have the same value as you mentioned "HR". And my suggestion is to have the id's of the table as reference instead of department names. Hope This helps.

Upvotes: 1

Yasitha
Yasitha

Reputation: 911

this is the query i used to do my work

and thanks goes to MR. Yuvvaraj Bathrabagu

$this->db->select('*'); $this->db->from('new_users'); $this->db->join('contact', 'new_users.pending_on = contact.dep','left'); $query = $this->db->get(); $data = $query->result_array();

Upvotes: 1

Related Questions