CODEWITHSUNDEEP
mysql
PhpGuy
PhpGuy

Reputation: 3

Mysql Query INNER JOIN

I have the following 2 tables :

Lead

LEADid
pmp
pms
ci_lead
av_lead
prod_lead
f_lead
sec_lead
pid

TeamMember

UID
fname
lname
email
d_phone
h_phone

All the data in the Lead table are int() that correspond to a UID from the TeamMember table.

So what I am trying to do is, say I select a row from the Lead table using the PID as my key, I want to pull all the fname and lname from TeamMembers that match the id's from the Lead table. I have tried INNER Joins to accomplish this, but always seem to output only the first id.

This is the latest query I have tried

SELECT `TeamMember`.`fname`,
       `TeamMember`.`lname`
  FROM `MaxusDash`.`Lead`
 INNER JOIN `MaxusDash`.`TeamMember`
    ON (`Lead`.`pmp` = `TeamMember`.`UID`)
   AND (`Lead`.`pms` = `TeamMember`.`UID`)
   AND (`Lead`.`ci_lead` = `TeamMember`.`UID`)
   AND (`Lead`.`av_lead` = `TeamMember`.`UID`)
   AND (`Lead`.`prod_lead` = `TeamMember`.`UID`)
   AND (`Lead`.`f_lead` = `TeamMember`.`UID`)
   AND (`Lead`.`sec_lead` = `TeamMember`.`UID`)
 WHERE `Lead`.`pid` = 44;

Thanks for any help.

Upvotes: 0

Views: 58

Answers (2)

spencer7593
spencer7593

Reputation: 108400

If you wanted to return all the names on a single row, then you'd need to use a separate join operation for each foreign key relationship.

But it appears you want to return each name on a separate row.

One approach is something like this:

 SELECT r.role
      , m.fname
      , m.lname
   FROM `MaxusDash`.`Lead` l
  CROSS
   JOIN (           SELECT 'pmp' AS `role`
          UNION ALL SELECT 'pms'
          UNION ALL SELECT 'ci_lead'
          UNION ALL SELECT 'av_lead'
          UNION ALL SELECT 'prod_lead'
          UNION ALL SELECT 'f_lead'
          UNION ALL SELECT 'sec_lead'
        ) r
   LEFT     
   JOIN `MaxusDash`.`TeamMember` m
     ON ( m.uid = l.pmp       AND r.role = 'pmp'       )
     OR ( m.uid = l.pms       AND r.role = 'pms'       )
     OR ( m.uid = l.ci_lead   AND r.role = 'ci_lead'   )
     OR ( m.uid = l.av_lead   AND r.role = 'av_lead'   )
     OR ( m.uid = l.prod_lead AND r.role = 'prod_lead' )
     OR ( m.uid = l.f_lead    AND r.role = 'f_lead'    )
     OR ( m.uid = l.sec_lead  AND r.role = 'sec_lead'  )

  WHERE l.pid = 44

Depending on the characterset of the role column, and your connection, you may need to specify (override the default) characterset of the string literals. For example, if the role is latin1, and your connection is UTF8, to avoid an "illegal mix of collations" error, you can preface the string literals with _latin1

                    SELECT _latin1'pmp' AS `role`
          UNION ALL SELECT _latin1'pms'

Remove the r.role expression from the SELECT list, if we don't need that value returned. Remove the LEFT keyword to use an inner join instead of an outer join.

Upvotes: 0

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

You have use UNION or create a JOIN for each lead member

UNION:

SELECT `pmp` as lead, `TeamMember`.`fname`, `TeamMember`.`lname`
FROM `MaxusDash`.`Lead`
INNER JOIN `MaxusDash`.`TeamMember` 
        ON (`Lead`.`pmp` = `TeamMember`.`UID`) 
WHERE `Lead`.`pid`=44
UNION  
SELECT `pms` as lead, `TeamMember`.`fname`, `TeamMember`.`lname`
FROM `MaxusDash`.`Lead`
INNER JOIN `MaxusDash`.`TeamMember` 
        ON (`Lead`.`pms` = `TeamMember`.`UID`) 
WHERE `Lead`.`pid`=44
UNION  
....
UNION
SELECT `sec_lead` as lead, `TeamMember`.`fname`, `TeamMember`.`lname`
FROM `MaxusDash`.`Lead`
INNER JOIN `MaxusDash`.`TeamMember` 
        ON (`Lead`.`sec_lead` = `TeamMember`.`UID`) 
WHERE `Lead`.`pid`=44

JOIN

SELECT `pmp`.`fname`, `pmp`.`lname`,
       `psp`.`fname`, `psp`.`lname`,
       ....
       `sec_lead`.`fname`, `sec_lead`.`lname`
FROM `MaxusDash`.`Lead` as `L`
INNER JOIN `MaxusDash`.`TeamMember` as `pmp`
        ON L.`pmp` = `pmp`.`UID`

INNER JOIN `MaxusDash`.`TeamMember` as `psp`
        ON L.`psp` = `psp`.`UID`

....
INNER JOIN `MaxusDash`.`TeamMember` as `sec_lead`
        ON L.`sec_lead` = `sec_lead`.`UID`

WHERE `L`.`pid`=44

Upvotes: 0

Related Questions