CODEWITHSUNDEEP
mysql
Taylor Foster
Taylor Foster

Reputation: 1195

JOIN statement or a different approach

I only included the columns in my table that pretty much relate to this so please look past the lack on columns in Users and Address tables.

To give a quick rundown on the system. The 'distributor' is the main tie in here, they are at the top of the role tree. In order to create a manager, you need to tie them to a distributor, hence the DistributorManagerRelationship table. When a rep is created, they must be tied to a manager and that manager can only be a manager that is associated with the selected distributor. So in the below table set, Live Labs is the only distributor that can create representatives, since there are managers under that account.

Address Table

addressId  firstName  lastName  companyName
  82         Mary       Mertens  IRON Co.
  83         John       Smith    Live Labs
  84         Lauren     Cole      NULL
  85         Sam        Little    NULL
  86         Wayne      Brady     NULL
  87         Ronald     Foster    NULL
  88         Shawn      Wood      NULL

Users Table

userId  active  username     role          addressId
  19       1     mary     distributor         82
  20       1     john     distributor         83
  21       1     lauren   manager             84
  22       1     sam      manager             85
  23       1     wayne    manager             86
  24       1     ronald   representative      87
  25       1     shawn    representative      88

DistributorManagerRelationship

distributorId  managerId
    20            21
    20            22
    20            23

ManagerRepRelationship

repId  managerId
  24       21
  24       22
  25       22

Here is the problem I am having. When a distributor is logged in and goes to see all Users under him (his managers and reps) the SQL statement I have will only grab representatives and managers that have a relationship in the ManagerRepRelationship table. So for example. 

SELECT DISTINCT Users.userId, Users.role, Users.username, Address.firstName, Address.lastName
FROM Users 
JOIN Address 
 ON Users.addressId=Address.addressId 
JOIN ManagerRepRelationship 
 ON Users.userId=ManagerRepRelationship.repId 
 OR Users.userId=ManagerRepRelationship.managerId 
JOIN DistributorManagerRelationship 
 ON Users.userId=DistributorManagerRelationship.managerId 
 OR ManagerRepRelationship.managerId=DistributorManagerRelationship.managerId 
WHERE DistributorManagerRelationship.distributorId=20 AND Users.userId!=20 ORDER BY Address.lastName

The above SQL statement will return all reps and managers associated with Live Labs EXCEPT userId 23 (Wayne Brady). I understand why that is, because Wayne does not match the parameters of the JOIN because he is lacking a representative. I have been battling this SQL statement for hours and I can't seem to get it right. The result I am wanting is all the managers and reps tied to, in this case, Live Labs even if there is no relationship in the ManagerRepRelationship table. Maybe I need to combine two different JOIN queries? I'm stuck.

I hope I did an ok job at explaining this, please comment if I didn't make anything clear enough.

Upvotes: 1

Views: 29

Answers (1)

Lajos Arpad
Lajos Arpad

Reputation: 76424

If you want to include people who do not have representatives, then you want to allow nulls for representative values, so instead of

JOIN ManagerRepRelationship

you need

LEFT JOIN ManagerRepRelationship

Upvotes: 1

Related Questions