[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4258
  • Last Modified:

MySQL Left Outer Join on Multiple Fields Relating to a Single Field in the Main Table

In MySQL5, how can I construct a single query (not a union query) that will show the Lastname of the primary *and* secondary account rep assigned to each supplier, with these tables?

SUPPLIERS
    id  company    primary_rep    secondary_rep
    --- -------------   ---------------    ------------------
    1   AAA                20                    12
    2   BBB                NULL                16
    3   CCC               10                    NULL
    4   DDD               17                    NULL
    5   EEE                15                    20

ACCOUNTREPS
id   lastname     firstname
---  -------------    -------------
10   Lance          Alvin
11   Mimms         Barry
12   Nunn           Cathy
13   O'Hare        Duane
14   Penn           Ellen
15   Quinn          Frank
16   Reems        Greg
17   Smith          Henry
18   Thomas       Ines      
19   Ulman          Jean
20   Velez           Katrina

The result should be like:

ACCOUNT REPS REPORT
   company    primary_rep    secondary_rep
   -------------   ---------------    ------------------
    AAA                Velez              Nunn
    BBB                                      Reems
    CCC               Lance              
    DDD               Smith                    
    EEE                Quinn             Velez

In PHP, I tried a query with a LEFT OUTER JOIN like the following, but it is wrong because it makes it look like the primary rep and secondary rep are the same person.

$query = "SELECT suppliers.id, suppliers.company, suppliers.primary_rep, suppliers.secondary_rep, accountreps.id, accountreps.lastname FROM suppliers LEFT OUTER JOIN accountreps ON suppliers.primary_rep = accountreps.ID OR customers.secondary_rep = accountreps.ID";

 $result = mysql_query($query, $db) or die(mysql_error());
   while ($row = mysql_fetch_assoc($result)) {
        $company = $row['company'];
        $primary_rep_name = $row['lastname'];
        $secondary_rep_name = $row['lastname'];
   }

It is *wrongly* showing this:

   company    primary_rep    secondary_rep
   -------------   ---------------    ------------------
    AAA                Velez              Velez
    BBB                                    
    CCC               Lance             Lance          
    DDD               Smith              Smith              
    EEE                Quinn             Quinn

But it should show this:
   company    primary_rep    secondary_rep
   -------------   ---------------    ------------------
    AAA                Velez              Nunn
    BBB                                      Reems
    CCC               Lance              
    DDD               Smith                    
    EEE                Quinn             Velez

How should the query be modified?
0
FrankTech
Asked:
FrankTech
  • 2
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you have to join 2 times, please check out this query:

$query = "SELECT s.id, s.company, s.primary_rep, s.secondary_rep, a1.lastname primary_lastname, a2.lastname secondary_lastname
FROM suppliers s
LEFT OUTER JOIN accountreps a1 ON s.primary_rep = a1.ID
LEFT OUTER JOIN accountreps a2 ON s.secondary_rep = a2.ID
"
0
 
FrankTechAuthor Commented:
angelIII,
    Thanks! To test it, how would I reference the two lastname fields as PHP $row elements in the "while" clause?  Currently it is like this, but I know it's wrong:

   while ($row = mysql_fetch_assoc($result)) {
        $company = $row['company'];
        $primary_rep_name = $row['lastname'];
        $secondary_rep_name = $row['lastname'];
   }
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you simply use the column alias names given in the query:
a1.lastname primary_lastname, a2.lastname secondary_lastname

hence:

   while ($row = mysql_fetch_assoc($result)) {
        $company = $row['company'];
        $primary_rep_name = $row['primary_lastname'];
        $secondary_rep_name = $row['secondary_lastname'];
   }
0
 
FrankTechAuthor Commented:
Works great. Thanks!
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now