search authors sql help?

Hi,

I need help with the sql.

I have a tblAuthor(authorid, surname,givenname)
tblAssociation(associationid,publicationid,authorid)
tblPublication(publicationid,title)


I am trying to find all other authors(coauthors) who worked on the same publcation with given an authorid.

ex) coauthors of author1 are 2,3,4,5
      coauthors of author2 are 1,5,7
     coauthors of author3 are 1,4
     coauthors of author4 are 1,3  etc
     
      publication1, author1,author3,author4  
      publciation2, author1,author2,author5
      publication3, author2,author1,author7
      publication4,author6,author7


 tblassociation(1,1,1)
 tblassociation(2,1,3)
 tblassociation(3,1,4)
 tblassociation(4,2,1)
 tblassociation(5,2,2)
 tblassociation(6,2,5)
 tblassociation(7,3,2)
 tblassociation(8,3,1)
 tblassociation(9,3,7)
 tblassociation(10,4,6)
 tblassociation(11,4,7)
dkim18Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
mbizupConnect With a Mentor Commented:
An alternative which might have better performance:


SELECT AuthorId
FROM tblassociation
INNER JOIN
(SELECT pubid FROM tblassociation
WHERE authorid= [enter author id]) AS a
ON tblassociation.PubID = a.PubID
0
 
mbizupCommented:
Select AuthorId
From tblassociation
where pubid in
(Select pubid from tblassociation
Where authorid= [enter author id]
0
 
mbizupCommented:
I missed a closing parenthesis in that:



Select AuthorId
From tblassociation
where pubid in
(Select pubid from tblassociation
Where authorid= [enter author id])
0
 
dkim18Author Commented:
I think that's what I got.

SELECT Affiliation.AuthorID, Author.Surname, Author.Given_Name
FROM Author INNER JOIN Affiliation ON Author.AuthorID = Affiliation.AuthorID
WHERE (((Affiliation.PublicationID) In (SELECT Affiliation.PublicationID FROM Affiliation WHERE Affiliation.AuthorID=[xxxx])));

Thanks for checking it out.
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.