my sql joint 2 table but 2 time

`idmemberref` int(11) NOT NULL,
  `idactivity` int(11) DEFAULT NULL,
  `manaferid` int(11) DEFAULT NULL,


Above is table of activitynote   it link to table member that have the name of member

How can i make sql work

`idmemberref` link to name of member  `manaferid is the menber that recommended member

how can I make sql work out put should like

`manaferid'             `idactivity`    `manaferid`
member.name                                member.name

David                             1                  robert
susan                             2                 nucharin
michele                          1                  hillary
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

Open in new window

teeraAsked:
Who is Participating?
 
Frank ContrepoisVP Technical SalesCommented:
my mistake, the first idactivity is for a2

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

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

Open in new window

0
 
Frank ContrepoisVP Technical SalesCommented:
Use aliases

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

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

Open in new window

0
 
teeraAuthor Commented:
Hi fcontrepois

Unknown column 'a1.idactivity' in 'field list'
0
 
teeraAuthor Commented:
Hi fcontrepois

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


give  the same result to every row
0
 
Frank ContrepoisVP Technical SalesCommented:
SELECT m1.name, an.idactivity, m2.name
FROM activitynote an 
JOIN member m1 ON an.idmemeberref = m1.idmember
JOIN member m2 ON an.manaferid = m2.idmember

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.