Solved

help with query?

Posted on 2012-03-23
8
265 Views
Last Modified: 2012-03-26
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
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
  • 4
  • 3
8 Comments
 
LVL 75

Assisted Solution

by:käµfm³d 👽
käµfm³d   👽 earned 50 total points
ID: 37758944
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
 

Author Comment

by:dkim18
ID: 37758959
I am providing the authorid.
0
 

Author Comment

by:dkim18
ID: 37758961
With given authorid, I am trying to find any other authors who are tied to that publicationid.
0
Technology Partners: 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!

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37759423
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37759430
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
 

Author Comment

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

Author Comment

by:dkim18
ID: 37762602
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
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 450 total points
ID: 37762821
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

Turn Insights into Action

Communication across every corner of your business is essential to increase the velocity of your application delivery and support pipeline. Automate, standardize, and contextualize your communication processes with xMatters.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

691 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