Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

search authors sql help?

Posted on 2012-03-25
4
Medium Priority
?
307 Views
Last Modified: 2012-03-25
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)
0
Comment
Question by:dkim18
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 37762609
Select AuthorId
From tblassociation
where pubid in
(Select pubid from tblassociation
Where authorid= [enter author id]
0
 
LVL 61

Expert Comment

by:mbizup
ID: 37762619
I missed a closing parenthesis in that:



Select AuthorId
From tblassociation
where pubid in
(Select pubid from tblassociation
Where authorid= [enter author id])
0
 
LVL 61

Accepted Solution

by:
mbizup earned 2000 total points
ID: 37762624
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
 

Author Comment

by:dkim18
ID: 37762632
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

604 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question