CODEWITHSUNDEEP
mysqljoin
blankip
blankip

Reputation: 340

MySQL, two columns sharing the same join

SELECT
COALESCE (reports_straddons.employeeid, ldap_karen.uid) as uid,
COALESCE (reports_straddons.thomslocationcountrydescr, ldap_karen.thomslocationcountrydescr, 'NOVALUE') as thomslocationcountrydescr,
COALESCE (reports_straddons.thomslocationcity, ldap_karen.thomslocationcity, 'NOVALUE') as thomslocationcity, 
COALESCE (reports_straddons.regionname, ldap_karen.regionname) as regionname,
CONCAT(reports_jobroles.groupabrev, COALESCE(CONCAT(' - ', reports_straddons.sup_role),'')) as sa_title,
ldap_karen.created
FROM
reports_straddons
LEFT JOIN ldap_karen ON reports_straddons.employeeid = ldap_karen.uid 
LEFT JOIN reports_jobroles ON reports_straddons.roleid = reports_jobroles.nameid

Right now I am getting the user ID, country, city, region, but the group (compounded roles of roleid - sup_role) is a bit jacked up. The group is a compound name that is made up of:

We have one job role table that we are linking to with names like Manager, Engineer, Sales, AM, PM, whatever.

So an example would be PM - Engineer. The roleid part I got working fine but not understanding what I have done wrong with the second part (and I have tried like 10 things which range from showing up anyone with a sup_role multiple times to only showing those with sup_roles - not everyone has both). So I have a join that is used for two distinct fields and both of those just contain the IDs to the joining table for the same column.

So right now I am getting Engineer - 9, 9 being the ID for the sup_role when it should read Developer if grabbing that name from the table based on the sup_role.

Note: I first logically used LEFT JOIN reports_jobroles ON reports_straddons.sup_role = reports_jobroles.nameid but alas this did not work at all.

Upvotes: 1

Views: 53

Answers (1)

mkasberg
mkasberg

Reputation: 17262

I think I understand. The problem is you need to join to a table twice, but you're only joining it once. A simplified version of your problem would look like this:

SELECT 
COALESCE (reports_straddons.employeeid, ldap_karen.uid) as uid,
CONCAT(reports_jobroles.groupabrev, COALESCE(CONCAT(' - ', reports_straddons.sup_role),'')) as sa_title
FROM
reports_straddons
LEFT JOIN reports_jobroles ON reports_straddons.roleid = reports_jobroles.nameid

To fix it, you need to do something like this:

SELECT 
COALESCE (reports_straddons.employeeid, ldap_karen.uid) as uid,
CONCAT(roles.groupabrev, COALESCE(CONCAT(' - ', suproles.groupabrev),'')) as sa_title
FROM
reports_straddons
LEFT JOIN reports_jobroles roles ON reports_straddons.roleid = roles.nameid
LEFT JOIN reports_jobroles suproles ON reports_straddons.sup_role = suproles.nameid

Notice how I'm joining to reports_jobroles twice, and naming it something different. I'm under the assumption that sup_role will join to the nameid field much like roleid.

Upvotes: 2

Related Questions