0

I'm creating an archive for Academic Papers. Each paper may have one author, or multiple authors. I've created the tables in the following manner:

  • Table 1: PaperInfo - Each row contains information on the paper
  • Table 2: PaperAuthor - Only Two Columns: contains PaperID, and AuthorID
  • Table 3: AuthorList - Contains Author Information.

There is also a Table 4 which is linked to Table 4, which contains a list of Universities which the author belongs to, but I'm going to leave it out for now in case it gets too complicated.

I wish to have a Query which will link all three tables together, and display Paper Information of the recordset in a table, with columns such as these:

  • Paper Title
  • Paper Authors

The column "Paper Authors" is going to contain more than one authors in some cases.

I've wrote the following query:

SELECT a.*,b.*,c.*
FROM PaperInfo a, PaperAuthor b, AuthorList c
WHERE a.PaperID = b.PaperID AND b.AuthorID = c.AuthorID

So far, the results I've been getting for each row is one author per row. I wish to contain more authors in one column. Can this be done in anyway?

Note: I'm using Access 2010 as my database.

huMpty duMpty
  • 14,346
  • 14
  • 60
  • 99
kosherjellyfish
  • 361
  • 4
  • 16
  • 1
    It depends on which SQL flavour you are using. For ex: This can be easily done in PostgreSQL(using array_agg function)/MySQL(using group_concat function). So, tells us what SQL database you are using. –  Sep 30 '13 at 12:07
  • Which DBMS are you using? Postgres? Oracle? –  Sep 30 '13 at 12:20

5 Answers5

1

In straight SQL the answer unfortunately is that it isn't possible. You would need to use a processing language in order to get the result you are after.

stuartp
  • 55
  • 3
1

Since you mention you are using Access 2010 please refer to this question: is there a group_concat function in ms-access?

Particularly, read the post which points to http://www.rogersaccesslibrary.com/forum/generic-function-to-concatenate-child-records_topic16&SID=453fabc6-b3z9-34z6zb14-a78f832z-19z89a2c.html

You probably need to implement a custom function but the 2nd url does what you are looking for.

Community
  • 1
  • 1
0

This functionality is not part of the SQL standard, but different vendors have solutions for it, see for instance Pivot Table with many to many table, MySQL pivot table.

Community
  • 1
  • 1
Neville Kuyt
  • 29,247
  • 1
  • 37
  • 52
0

If you know the maximum number of authors per paper (for example 3 or 4), you could get away with a triple or quadruple left join.

Nicolas Constant Brix
  • 121
  • 3
-1

What you are after is an inner join.

An SQL JOIN clause is used to combine rows from two or more tables, based on a common field between them. The most common type of join is: SQL INNER JOIN (simple join). An SQL INNER JOIN return all rows from multiple tables where the join condition is met.

http://www.w3schools.com/sql/sql_join.asp

You may want to combine the inner join with a group to give you 1 paper to many authors in your results.

The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns.

http://www.w3schools.com/sql/sql_groupby.asp

Gusdor
  • 14,001
  • 2
  • 52
  • 64