SQL JOIN with two tables and with a condition for one table, in a single SQL statement

Question

I have an sqlite database which has a table of documents (journal articles), where each document has a unique id. All the authors are listed in another table, with corresponding document id's, and with unique author id's as well. The problem is that articles have different number of authors. If there is one author, I should retrieve just his name. If there are two, I should retrieve both names. If there are more than two, I need to retrieve only the first author and append "et al." to it.

In the following example, I need to produce records of the type "Authors - Title". For Document Title1 I shoud have "Name101 et al. - Title1", for Title2 - "Name201 - Title2", for Title3 - "Name301 & Name302 - Title3".

DocsTable
Title   |  DocId
Title1  |  10
Title2  |  20
Title3  |  30

AuthorsTable
Name      | AuthorId  | DocId
Name101   |    101    | 10
Name102   |    102    | 10
Name103   |    103    | 10
Name201   |    201    | 20
Name301   |    301    | 30
Name302   |    302    | 30

I certainly might do that with at least two queries, but how might I do that in a single statement?

Another related problem is that I'd like to sort the result by author names with accent-insensitive order (where, e.g., ú = ü = u). I am aware about COLLATE Latin1_General_CI_AI, and it works fine in LIKE clause, however when I write ORDER BY names COLLATE Latin1_General_CI_AI, the statement returns error.

Will you only ever have up to two authors? If so then you could use a CASE statement to specify the logic you want to update to. If more than 2 authors then it could get more complicated. — , Dec 28 '15 at 17:51
I do not need to update tables (if you mean that). I need to query a document from the table DocsTable, and see how many authors this document has in the AuthorsTable. If one or two authors, I need to query their names, if three, I need the name of the first author only. In fact, I might query all the authors of the document, but that will yield the result sets with variable column number - I'm not sure how to deal with that either. — Maximko, Dec 28 '15 at 17:58
Why is it Title3 - "Name301 & Name302 - Title3" and not Title3 - "Name302 & Name301 - Title3"? IF Name301 is the lead author then maybe you need another column to mark the lead author. — oks, Dec 28 '15 at 18:28

Gordon Linoff · Answer 1 · 2015-12-29T23:05:01.860

There is no such thing as the "first" author on a SQL table. SQL tables represent unordered sets. The closest you can get in SQLite is to use rowid.

select docId,
       (case when count(*) = 1 then min(name)
             when count(*) = 2
             then max(case when rowid = minrowid then name end) || ' & ' ||
                  max(case when rowid <> minrowid then name end)
             else max(case when rowid = minrowid then name end) || ', et al'
         end) as authors
from AuthorsTable a left join
     (select a2.docid, min(rowid) as minrowid
      from AuthorsTable a2
      group by a2.docid
     ) aa
     on a.docid = a2.docid
group by docid;

EDIT:

Based on the comment that the ordering is by authorid:

select docId,
       (case when count(*) = 1 then min(name)
             when count(*) = 2
             then max(case when AuthorId = minAuthorId then name end) || ' & ' ||
                  max(case when AuthorId <> minAuthorId then name end)
             else max(case when AuthorId = minAuthorId then name end) || ', et al'
         end) as authors
from AuthorsTable a left join
     (select a2.docid, min(AuthorId) as minAuthorId
      from AuthorsTable a2
      group by a2.docid
     ) aa
     on a.AuthorId = a2.minAuthorId
group by docid;

I don't quite get it (sorry, I'm only two weeks with SQL). Do you join the _same_ two tables? You never mention DocsTable in the query. Also, what is "aa" in the third line from below? — Maximko, Dec 29 '15 at 12:30
As for the "first" author - in fact, the AuthorId in the AuthorsTable is exactly for that - if I ORDER BY AuthorId ASC, I get the needed order of the authors for the given document specified by DocId — Maximko, Dec 29 '15 at 12:32
@Maximko . . . The `DocsTable` doesn't seem necessary for this query. — Gordon Linoff, Dec 29 '15 at 23:05

SQL JOIN with two tables and with a condition for one table, in a single SQL statement

1 Answers1