CODEWITHSUNDEEP
sql
Marc Anthony Melgar
Marc Anthony Melgar

Reputation: 31

show result from one table

good day, i have these 3 tables...i.e.;

customer table

cust_id cust_name sales_employee
1       abc       1
2       cde       1
3       efg       2

transaction table

order_num   cust_id   sales_employee
1001        1         1 
1002        2         2

sales_employee table

sales_employee     employee name
1                  john doe
2                  jane doe

how can i show the employee name on both customer table and transaction table? notice how the sales_employee can change per transaction, it does not necessarily have to be the same per customer.

please help.

Upvotes: 3

Views: 51

Answers (1)

Brian Webster
Brian Webster

Reputation: 30865

To select customers with sales person name

select 
  C.*, E.employee_name
from
  Customers as C
  inner join Sales_Employees as E on E.sales_employee = C.sales_employee

To select transactions with customer name and salesperson name (at the point in time of the transaction)

select 
  T.*, 
  E.employee_name as Trans_employee, 
  C.cust_name,
  EC.employee_name as Cust_employee
from
  Transactions as T
  inner join Sales_Employees as E on E.sales_employee = T.sales_employee
  inner join Customers as C on C.cust_id= T.cust_id
  inner join Sales_Employees as EC on EC.sales_employee = C.sales_employee

This code is meant to guide you, you will need to adjust it to match your table and field names.

Upvotes: 1

Related Questions