• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 272
  • Last Modified:

help with query?

I need a little help with sql query.

I have the tblAuthor(authorID,surname,givenname)
tblAssociation(associationID, publicationID,AuthorID)

I am trying to list coauthors here of given authorID.

Authors are tied to publication.

select authorID,surname,givenname from tblauthor a  inner join tblAssociation b on
a.authorid=b.authorid where b.publicationid in (
select publid from association where authorid=xxx)

Is that correct?
0
dkim18
Asked:
dkim18
  • 4
  • 3
2 Solutions
 
käµfm³d 👽Commented:
I don't think you need the inner query. Try:

SELECT auth.givenname, auth.surname
FROM tblAuthor auth
INNER JOIN tblAssociation asoc
ON auth.authorID = asoc.authorID
WHERE asoc.publicationID = xxx

Open in new window

0
 
dkim18Author Commented:
I am providing the authorid.
0
 
dkim18Author Commented:
With given authorid, I am trying to find any other authors who are tied to that publicationid.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Rey Obrero (Capricorn1)Commented:
try this query

select a.authorID,a.surname,a.givenname, b.associationID
from tblauthor a  inner join tblAssociation b on
a.authorid=b.authorid where authorID=xxx
0
 
Rey Obrero (Capricorn1)Commented:
select a.authorID,a.surname,a.givenname, b.associationID,b.publicationid
from tblauthor a  inner join tblAssociation b on
a.authorid=b.authorid
where a.authorID=xxx
0
 
dkim18Author Commented:
Your sql finds all the publication for a search author.


I am trying to find all other authors who worked on that same publication with given an authorid.

ex) 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
 
dkim18Author Commented:
This brings back what I want.

How do I tie this up to the author main form?
If author main form's id is 3, how do I pass this to the subform whose data source is this query?





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])));
0
 
Rey Obrero (Capricorn1)Commented:
you are posting different table names from your original post and up to your last post.

this is not a good way to present your problem..

anyway, see this link to help you get your main/sub form working


http://office.microsoft.com/en-us/access-help/create-a-form-that-contains-a-subform-a-one-to-many-form-HA010098674.aspx?CTT=3
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now