CODEWITHSUNDEEP
phpmysqlsqlcodeigniter
Imnotapotato
Imnotapotato

Reputation: 5808

MySQL: How to display 2 u_names from an 'expenses' table which contains 2 users displays (Pulling 2 different rows from 'users' table)?

I am trying to build an expenses request system where a user can request from a supervisor for an expense.

So I have a users table: 

u_id - primary key
u_name 
u_email 
u_password 
u_permissions 
u_sp (if supervisor [Boolean])

and an expenses table: 

exp_id - primary key  
exp_user (index - contains the user ID)   
exp_sp (index - contains the user ID) 
exp_date
exp_amount

I want to display a table with the expenses but instead of showing the id's of both users (exp_user and exp_sv) - I want to display their name for a more comfortable view.

I thought of using JOIN as usual (Ex: SELECT * FROM expenses JOIN users ON expenses.exp_user=users.u_id) but I noticed that I need a different one for (exp_sv) so this wont work. How do I get this query right with a way to distinguish between the 2 rows from the same users table?

I use CodeIgniter so this is the current code part in my model: 

// Get "expenses" table  from DB.
public function get_expenses_table() { 

    /*$query = $this->db->get('expenses');
    $result = $query->result();
    return $result;*/

    $query - $this->db->query("SELECT * FROM expenses 
                                                JOIN users 
                                                ON expenses.exp_user=users.u_id ");

}

Upvotes: 1

Views: 51

Answers (2)

Jay Prakash
Jay Prakash

Reputation: 805

Please try this : 

 SELECT e.exp_id,e.exp_user,e.exp_sp,e.exp_date,e.exp_amount,
 u.u_name 
 FROM expenses e,users u
 where e.exp_user=u.u_id

Upvotes: 0

vher2
vher2

Reputation: 990

You can join them like this:

SELECT 
    e.*, 
    u1.u_name user_name,
    u2.u_name supervisor_name
FROM expenses e
LEFT JOIN users u1 on u1.u_id = e.exp_user
LEFT JOIN users u2 on u2.u_id = e.exp_sp

Upvotes: 2

Related Questions