CODEWITHSUNDEEP
mysqlsql
Chip
Chip

Reputation: 107

select data from multiple tables by matching multiple columns

I have 3 tables like so:

Table User

    +---------+-----------------+
    | UserId  | UserName        |
    +---------+-----------------+
    | 1       | [email protected]    |
    | 2       | [email protected]    |    
    | 3       | [email protected]  |  
    | 4       | [email protected]   |
    +---------+-----------------+

Table Employee

    +-------------+----------------+------------+----------+---------+
    | EmployeeId  | Email          | Department | Position | Duty    |
    +-------------+----------------+------------+----------+---------+
    | 1           | [email protected]   | Accounting | Manager  | Aproval |
    | 2           | [email protected]   | Accounting | Manager  | NULL    |
    | 3           | [email protected] | Marketing  | Staff    | NULL    |
    | 4           | [email protected]  | Purchasing | Staff    | NULL    |
    +-------------+----------------+------------+----------+---------+

Table Authorization

    +------------------+----------------+------------+----------+----------+
    | AuhtorizationId  | Level          | Department | Position | Duty     |
    +------------------+----------------+------------+----------+----------+
    | 1                | 1              | Accounting | Manager  | NULL     |
    | 2                | 2              | Marketing  | Staff    | NULL     |
    | 3                | 3              | Purchasing | Staff    | NULL     |
    | 4                | 4              | Accounting | Manager  | Approval |
    +------------------+----------------+------------+----------+----------+

How to construct a MySQL query to retrieve UserId, UserName/Email, Level, Department, and Position?

Upvotes: 0

Views: 175

Answers (3)

Justin
Justin

Reputation: 9724

Query:

SQLFiddleExample

SELECT 
u.`UserID`, 
u.`UserName`, 
a.`Level`, 
e.`Department`, 
e.`Position` 
FROM `User` u   
  LEFT JOIN `Employee` e
    ON u.`UserId` = EmployeeID 
  LEFT JOIN `Authorization` a 
    ON u.`UserId` = a.`AuhtorizationId`

Result:

| USERID |       USERNAME | LEVEL | DEPARTMENT | POSITION |
-----------------------------------------------------------
|      1 |   [email protected] |     1 | Accounting |  Manager |
|      2 |   [email protected] |     2 | Accounting |  Manager |
|      3 | [email protected] |     3 |  Marketing |    Staff |
|      4 |  [email protected] |     4 | Purchasing |    Staff |

Upvotes: 0

anuj arora
anuj arora

Reputation: 831

SELECT User.UserID, User.UserName, Authorization.Level, Authorization.Department, Authorization.Position FROM User INNER JOIN Employee ON User.UserId=Employee.EmployeeID INNER JOIN Authorization ON Employee.Department = Authorization.Department WHERE Employee.Position=Authorization.Position AND Employee.Duty=Authorization.Duty

Upvotes: 0

lvil
lvil

Reputation: 4326

If I understand correctrly all the table relations 

  SELECT UserID, UserName, Level, Department, Position FROM User   
INNER JOIN Employee ON UserId=EmployeeID INNER JOIN Authorization ON UserId = AuhtorizationId

Upvotes: 1

Related Questions