Similar queries to create third column in MySQL using JOIN

Question

Using this question, I've been trying to experiment with using different types of JOINS to try to combine these two SELECT queries. They are very similar and work fine by themselves but when I try to create a third column EmpFirstName3 the query blows up. How do I successfully combine these two tables?

HTG_TechProps Table

EmpNumber     |     EmpFirstName

111           |     Bob

222           |     John

333           |     Randy

HTG_TechStaffSets Table

EmpNumber     |     StaffSetID

CCN31         |     111

CCN11         |     222

POWW          |     Null

/* Techs */
    SELECT 
        p.EmpNumber,
        p.EmpFirstName AS EmpFirstName1,      
        t.EmpFirstName AS EmpFirstName2
    FROM HTG_TechProps p 
    LEFT JOIN HTG_TechStaffSets s ON p.EmpNumber=s.EmpNumber
    LEFT JOIN HTG_TechProps t ON t.EmpNumber=s.StaffSetID
    ORDER BY p.EmpNumber


/* Staff Sets */


SELECT 
    p.EmpNumber,
    p.EmpFirstName AS EmpFirstName1,      
    t.EmpFirstName AS EmpFirstName2
FROM HTG_TechProps p 
LEFT JOIN HTG_TechStaffSets s ON p.EmpNumber=s.StaffSetID
LEFT JOIN HTG_TechProps t ON t.EmpNumber=s.EmpNumber
ORDER BY p.EmpNumber

I tried that based on the question I found on Stack but I'm clearly goofing it up. Can you venture a sample please @ItalianStallion — Rocco The Taco, Sep 10 '14 at 20:02
I'm totally lost. You join techProps to Staff sets on empNumber but from your sample data empNumber is not the same between the two tables. Then you join empnumber and staffsetid. — SQLChao, Sep 10 '14 at 20:09
I know, its totally jacked up. I have no control over the tables. In some cases the StaffSetID matches the EmpNumber and in some cases it does not...it's enough to drive me crazy LOL — Rocco The Taco, Sep 10 '14 at 20:13

score 1 · Accepted Answer · answered Sep 10 '14 at 20:05

Try this:

    SELECT 
        p.EmpNumber,
        p.EmpFirstName AS EmpFirstName1,      
        t.EmpFirstName AS EmpFirstName2
    FROM HTG_TechProps p 
    LEFT JOIN HTG_TechStaffSets s ON p.EmpNumber=s.EmpNumber
    LEFT JOIN HTG_TechProps t ON t.EmpNumber=s.StaffSetID

UNION ALL

SELECT 
    p.EmpNumber,
    p.EmpFirstName AS EmpFirstName1,      
    t.EmpFirstName AS EmpFirstName2
FROM HTG_TechProps p 
LEFT JOIN HTG_TechStaffSets s ON p.EmpNumber=s.StaffSetID
LEFT JOIN HTG_TechProps t ON t.EmpNumber=s.EmpNumber

ORDER BY p.EmpNumber

Oh man, talk about me over complicating things...I was trying INNER JOINS and UNIONS withing the queries. Thanks SOOOO MUCH! — Rocco The Taco, Sep 10 '14 at 20:07

Similar queries to create third column in MySQL using JOIN

1 Answers1