CODEWITHSUNDEEP
mysql
user1040364
user1040364

Reputation: 103

mysql main table link 2 field to the same table

I have a main table 'activitynot' it have 2 fields that link to the samtable 'member'.

How can I get the name of from member table the code below it give a correct name for member.Sname but

(SELECT member.Sname
FROM activitynote
INNER JOIN member
ON activitynote.idmemberref = member.idmember limit 1 )

it give the same name for every row plese correct it

SELECT member.Sname, activitynote.idactivity, (SELECT member.Sname
FROM activitynote
INNER JOIN member
ON activitynote.idmemberref = member.idmember limit 1 )

FROM activitynote
INNER JOIN member
ON activitynote.idactivity = member.idmember

Upvotes: 0

Views: 88

Answers (1)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115550

You need to make it a correlated subquery:

SELECT member.Sname
     , activitynote.idactivity
     , ( SELECT member.Sname
         FROM member                       --- JOIN removed
         WHERE activitynote.idmemberref = member.idmember 
         LIMIT 1 
       ) AS member2_name
FROM activitynote
  INNER JOIN member
    ON activitynote.idactivity = member.idmember

or join to the member table twice:

SELECT member.Sname
     , activitynote.idactivity
     , member2.Sname AS member2_name
FROM activitynote
  INNER JOIN member
    ON activitynote.idactivity = member.idmember
  LEFT JOIN member AS member2
    ON activitynote.idmemberref = member2.idmember

Upvotes: 1

Related Questions