Best way to write this SQL

[jrw4]

So I have three tables:

 

paper (pid, pname, pyear, p_conference)

author (aid, aname, a_institution)

co_author (aid, pid, co_author_number)

 

So papers stores the papers written and author stores all the authors.  Co_author links together all of the multiple people who could write the same paper.  So in that table you could have:

353 71 1

312 71 2

Which means that aid 357 is the first author of paper 71.  And Aid 312 is the second author of paper 71.

 

Now I am trying to write some queries to select from these three tables and I cannot think of the best way to do this.

 

My first query is:

 

Given the author id of an author, find all of his co-authors and they papers they wrote together.

 

What do you think the most optimal way to write this query is?

 

[jrw4]

I just want to point out that I am using this SQL currently:

 

SELECT aid, COUNT(*) as papers
FROM co_author
WHERE aid <> 467 AND
	pid IN (SELECT pid
			FROM co_author
			WHERE aid = 467)
GROUP BY aid

[JustLikeIcarus]

Something like this?  Just a note i changes all of the tables to plural names to make more sense to me.  Try this see what you get without having data to play with i cant be sure.  I also didnt use the normal "JOIN" syntax.

 

select papers.pname, authors.aname
from papers, authors, co_authors
where papers.pid = co_authors.pid
and co_authors.aid = authors.aid
and papers.pid in (select pid from co_authors where aid = 457)

[kickstart]

Hi

 

Have a feeling I have missed something, but this seems to cover it

 

SELECT *
FROM (SELECT pid FROM co_author WHERE aid = 353) a
JOIN co_author b
ON a.pid = b.pid
JOIN paper c
ON b.pid = c.pid

 

Basically a subselect to find all the papers the author has been involved in, then join that with the co_author table to get all the other authors involved in that paper and then join that with the papers table to get the details of those papers.

 

All the best

 

Keith

[fenway]

Interesting... why the subselect instead of simply using a WHERE clause on "b".

[kickstart]

Interesting... why the subselect instead of simply using a WHERE clause on "b".

 

That is probably the thing I was missing!

 

All the best

 

Keith