CODEWITHSUNDEEP
sqlselect
tavlima
tavlima

Reputation: 189

Select all Users related to Company

First of all, sorry for the meaningless title. Couldn't think of anything better. Please, take a look at my following tables:

User

+-----+-------+---------------+
|  id | name  | email         |
+-----+-------+---------------+
|   1 | NameA | [email protected] |
|   2 | NameB | [email protected] |
|   3 | NameC | [email protected] |
+-----+-------+---------------+

Department

+-----+---------+-------+---------+
|  id | company | name  | manager |
+-----+---------+-------+---------+
|   1 |       1 | DeptA |       1 |
|   2 |       1 | DeptB |       2 |
+-----+---------+-------+---------+

Company

+-----+------+-------+
|  id | name | owner |
+-----+------+-------+
|   1 | Buzz |     3 |
+-----+-------+------+

I need to find all users related to a Company. Something like:

+---------+------------+
| user_id | company_id |
+---------+------------+
|       1 |          1 |
|       2 |          1 |
|       3 |          1 |
+---------+------------+

How can I do that? I read about the different kind of joins (inner, outer, full, etc), but I couldn't figure out how to handle that "Department" table in the middle of everything.

Upvotes: 0

Views: 316

Answers (3)

WarrenT
WarrenT

Reputation: 4532

Typically, you would have some way to link Users to the Department they work in. Either with a DeptID column added onto the User table. In this example, the owner's department is represented as null, while the department of managers is populated, but which way you do this is up to you. 

 User
+-----+-------+---------------+------+
|  id | name  | email         | dept |
+-----+-------+---------------+------+
|   1 | NameA | [email protected] |    1 |
|   2 | NameB | [email protected] |    2 |
|   3 | NameC | [email protected] |  +++ |   
|   4 | NameD | [email protected] |    2 |
|   5 | NameE | [email protected] |    2 |
+-----+-------+---------------+------+

So to get the list of all company 1 users where the department is populated on the User table, this becomes simple

SELECT u.id       as user_id,
       d.company  as company_id
  FROM User u
  INNER JOIN Department d   ON u.dept=d.id
  WHERE d.company=1

UNION

SELECT owner  as user_id,
       id     as company_id
  FROM Company

If you chose not to populate manager's department in the User table, you would have to append a UNION for that part as well, similar in construct to the first part directly above.

Or you might instead have an association table to show which employees work for which department. This might be needed if a person could work in more than one department. (The managers and owners might not need to be in this table, since they are already accounted for. Again, up to you. But if you do include them, then you wont need the UNIONs, so it will keep things simpler. You might even put the owner in an "OWNERSHIP" department.)

 UserDept
+--------+--------+
| UserID | DeptID |
+--------+--------+
|    4   |    2   |
|    5   |    2   |
+--------+--------+

SELECT u.id       as user_id,
       d.company  as company_id
  FROM UserDept u
  INNER JOIN Department d   ON u.dept=d.id
  WHERE d.company=1

(Add UNIONs on as needed, if you did not include owners / managers in the UserDept table.)

But in case you need a deeper example, let's suppose you also needed the name from the User table:

SELECT u.id       as user_id,
       d.company  as company_id,
       u.name     as user_name
  FROM User u
  INNER JOIN UserDept x     ON u.id=x.user
  INNER JOIN Department d   ON x.dept=d.id
  WHERE d.company=1

Upvotes: 0

jediCouncilor
jediCouncilor

Reputation: 828

select User.id, User.name, User.email, CASE WHEN User.id = Company.Owner THEN 'YES' ELSE 'NO' END as CompanyOwner
from User
inner join Department on User.DepartmentID = Department.id
inner join Company on Company.id = Department.company

I think you're missing an entitiy relationship between Users and Departments, perhaps a departmentID on the User Table. I think that might be the part you're missing.

Upvotes: 0

Blorgbeard
Blorgbeard

Reputation: 103457

I would do this with two queries:

select Department.manager as user_id, Company.id
from Company
join Department on Department.company=Company.id

union

select Company.owner as user_id, Company.id
from Company

Upvotes: 3

Related Questions