delimiter $$
CREATE TABLE `leaders2subordinates` (
`leader_uid_number` int(11) NOT NULL COMMENT 'Leaders user number',
`leader_collection_id` smallint(4) NOT NULL COMMENT 'leaders type designation',
`sub_uid_number` int(11) NOT NULL COMMENT 'Subordinate uid_number',
`sub_collection_id` smallint(4) NOT NULL COMMENT 'Subordinate user type',
PRIMARY KEY (`leader_uid_number`,`leader_collection_id`,`sub_uid_number`,`sub_collection_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1$$
That has this data
[TABLE=“class: grid”]
[TR]
[TD]leader_uid_number[/TD]
[TD]leader_collection_id[/TD]
[TD]sub_uid_number[/TD]
[TD]sub_collection_id[/TD]
[/TR]
[TR]
[TD]10006[/TD]
[TD]9[/TD]
[TD]10004[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]10007[/TD]
[TD]10[/TD]
[TD]10005[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]10008[/TD]
[TD]8[/TD]
[TD]10006[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]10009[/TD]
[TD]8[/TD]
[TD]10007[/TD]
[TD]10[/TD]
[/TR]
[/TABLE]
I have a leads table where one of its’ fields is counselor_id; say for this example it is 10004
I have a users table where all users designated by 10004 - 10009 are located. This users table includes u.first_name and u.last_name fields
I can get a leads counselor by the following:
SELECT
CONCAT(cnsl.first_name, ' ', cnsl.last_name) as 'Counselor'
FROM
leads as l
INNER JOIN
users as cnsl
ON
l.counselor_id = cnsl.uid_number
WHERE
l.lead_id = 1;
This returns ‘Bill Parker’ as the Counselor.
But things get more complicated from here as I also want to find the that same leads Sales Manager I have to determine who ‘Bill Parkers’ Sales Manager.
To do this I need to involve the leaders2subordinates table. If ‘Bill Parker’ has an uid_number of 10004 and I want to return his Sales Manager then do I have enough information to return the Leads’ counselor and the sales manager related to that leads counselor? If so what would be the best way to return a row like:
What is the connection between lead counselor and sales manager?
Where do you get the ‘lead’ Paulette Johnson from? Is there another userid in the leads table?
In the table you posted you have leader and subordinate, but in your explanation you never use those terms. So the relation between what you want to achieve and the tables/columns is not clear.
Anyway, if all names come from the users table, all you have to do is join the user table 2 times more (once for the sales manager, once for the lead) and of course the leaders2subordinates table.
The sales manager manages the couselor that is assigned to a given lead.
The lead Paulette Johnson comes from the leads table; currently the only userid specified for a lead is the couselor userid, so there are not any further userids.
A lead has a counselor. A counselor has a sales manager. leads do not have sales managers they are only linked to a sales manage by proxy of the counselor.
Sorry, The leaders2subordinates table is a way to link the different hierarchies in the system. For example Managing Directors manage Location Managers, Location Manager manage Sales Managers, Sales Manager manage Counselors.
As there are many Sales Managers and even more Counselors, the leaders2subordinates table is a way to manage these hierarchies and to allow fast remapping as counselors and Sales Managers change often.
All names don’t come from the users table only Managing Directors, Location Managers, Sales Managers, and Counselors are in the users table and all leads are in the leads table.
SELECT
CONCAT(cnsl.first_name, ' ', cnsl.last_name) as 'Counselor'
FROM
leads as l
INNER JOIN
users as cnsl
ON
l.counselor_id = cnsl.uid_number
LEFT OUTER JOIN
leaders2subordinates as ldr2subs
ON
l.counselor_id = ldr2subs.sub_uid_number
INNER JOIN
users as sls
ON
l.counselor_id = sls.uid_number
WHERE
l.lead_id = 1;
This doesn’t give me a way to get the ldr2subs .leader_uid_number or return the Sales Manager name from the users table?
Sorry for my ‘slowness’ but if I need to find the leader_uid_number of the counselor associated with a lead then it involves two rows of the leaders2subordinates:
User 10004 is ‘Bill Parker’ which I can return using
SELECT
CONCAT(cnsl.first_name, ' ', cnsl.last_name) as 'Counselor'
FROM
leads as l
INNER JOIN
users as cnsl
ON
l.counselor_id = cnsl.uid_number
But when I add the other joins
SELECT
CONCAT(cnsl.first_name, ' ', cnsl.last_name) as 'Counselor'
FROM
leads as l
INNER JOIN
users as cnsl
ON
l.counselor_id = cnsl.uid_number
LEFT OUTER JOIN
leaders2subordinates as ldr2subs
ON
l.counselor_id = ldr2subs.sub_uid_number
INNER JOIN
users as sls
ON
l.counselor_id = sls.uid_number
WHERE
l.lead_id = 1;
I still can’t get the sls.first_name or sls.last_name (Sales Manager Name) for the leader_uid_number of 10006, right?
Well when I run the two subquery then it works but is this the best way? As I understand it Sub queries should be avoided
SELECT
CONCAT(cnsl.first_name, ' ', cnsl.last_name) as 'Counselor'
,(SELECT
CONCAT(sls.first_name, ' ', sls.last_name)
FROM
users as sls
WHERE
sls.uid_number = (SELECT
ldr2subs.leader_uid_number
FROM
leaders2subordinates as ldr2subs
WHERE
l.counselor_id = ldr2subs.sub_uid_number)
) as 'Sales Manager'
FROM
leads as l
INNER JOIN
users as cnsl
ON
l.counselor_id = cnsl.uid_number
WHERE
l.lead_id = 1;
This provides the correct result:
[TABLE=“class: grid”]
[TR]
[TD]Counselor[/TD]
[TD]Sales Manager[/TD]
[/TR]
[TR]
[TD]Bill Parker[/TD]
[TD]Anita Ward[/TD]
[/TR]
[/TABLE]
Is this what you and guido2004 meant by adding them to the SELECT, it seem that you guys where inferring that it should be simple (maybe two subqueries are considered simple)? Although given how I explained this the relationships still might not have been clear enough?
SELECT l.something AS leadname
, CONCAT(cnsl.first_name, ' ', cnsl.last_name) AS 'Counselor'
, CONCAT(sls.first_name, ' ', sls.last_name) AS 'Sales Manager'
FROM leads AS l
INNER
JOIN users AS cnsl
ON cnsl.uid_number = l.counselor_id
INNER
JOIN leaders2subordinates AS ldr2subs
ON ldr2subs.sub_uid_number = l.counselor_id
INNER
JOIN users AS sls
ON sls.uid_number = ldr2subs.leader_uid_number
WHERE l.lead_id = 1;
read that carefully, and notice which columns are joined to which columns – there is a method to the way they are joined
i find that a few moments thinking about an sql problem can save much later hardship…
here was the secret to this problem – a many-to-many table is usually joined twice in a query, and in this case, both joins are to the same table, the users table, but the trick is, it has to be done using different keys
i think my coding style helps me to see this kind of thing more easily, because each ON clause mentions first the column from the table being joined, and then, on the right side of the comparison, the column from a previously-mentioned table, above this point in the FROM clause
stating the join columns in this sequence in the ON clause reinforces the joining strategy, and of course the joining strategy is what solves the problem
then spotting the wrong join column is a lot easier
Yes if your thoughts are on the right track… problem was I was too focused that there was not a direct relationship between the counselor_id and eventually the correct leader_uid_number so my thoughs were too sequencial (in the wrong way)
Very solid rationelle, I am adopting this style to help me moving forward, so thanks!
