CODEWITHSUNDEEP
phpmysql
Bobby
Bobby

Reputation: 23

Linking tables with JOIN

I have 3 tables; memberID, membership, person

I need firstname lastname from 'membership' and MemberID and PersonID from 'memberID' The following gives me a list of names. 

   SELECT 
   CONCAT(P.FirstName, ' ', P.LastName) as FullName
   FROM `membership` M
   INNER JOIN `memberID` MI ON M.`MembershipID` = MI.`MembershipID`
   INNER JOIN `person` P ON MI.`PersonID` = P.`PersonID`
   AND P.`IsActive` =1

The following fails, how do I add the PersonID and MembershipID columns, . I believe I require another INNER JOIN but I don't know how to construct it.

   SELECT
   CONCAT(P.FirstName, ' ', P.LastName) as FullName
   FROM `membership` M
   MI.'membershipID' MI.'PersonID' FROM  'membershipID' MI
   INNER JOIN `memberID` MI ON M.`MembershipID` = MI.`MembershipID`

   INNER JOIN `person` P ON MI.`PersonID` = P.`PersonID`
   AND P.`IsActive` =1

Upvotes: 0

Views: 38

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Presumably those columns are already in the tables being referenced:

SELECT CONCAT(P.FirstName, ' ', P.LastName) as FullName,
       MI.PersonID, M.`MembershipID`
FROM `membership` M INNER JOIN
     `memberID` MI
     ON M.`MembershipID` = MI.`MembershipID` INNER JOIN
     `person` P
     ON MI.`PersonID` = P.`PersonID` AND P.`IsActive` = 1;

You don't need additional joins, just additional columns in the select.

Upvotes: 1

Related Questions