CODEWITHSUNDEEP
sqldatabasesqlite
anon1234
anon1234

Reputation: 13

Sqlite query comparison multiple times

I have the following schemas (sqlite):

I'm trying to get the names of all authors who have number of conferences articles >= journal articles in every year from 2000-2018 inclusive. If an author has 0 articles in each category in a year then the condition still holds. The only years that matter are 2000-2018

The query would be much easier if it was over all years since I could count the journal articles and conferences articles and make a comparison then get the names. However, I'm stuck when trying to check over every year 2000-2018.

I of course don't want to do repetitive queries over all the years. I feel like I may need to group by year but I'm not sure. So far I've been able to get all articles of both types from 2000-2018 as one large table but I'm not sure what to do next.:

select articleID, year
from JournalArticle
where year >= 2000 and year <= 2018

union

select articleID, year
from ConferenceArticle
where year >= 2000 and year <= 2018

Upvotes: 1

Views: 293

Answers (2)

LukStorms
LukStorms

Reputation: 29647

Sounds like you could use a COALESCE in the GROUP BY

SELECT a.name, 
COALESCE(j.year, c.year) as "year",
COUNT(j.articleID) AS JournalArticles,
COUNT(c.articleID) AS ConferenceArticles
FROM Author a
LEFT JOIN JournalArticle j ON (j.articleID = a.articleID AND j.year BETWEEN 2000 AND 2018)
LEFT JOIN ConferenceArticle c ON (c.articleID = a.articleID AND c.year BETWEEN 2000 AND 2018)
WHERE (j.year IS NOT NULL OR c.year IS NOT NULL)
GROUP BY a.name, COALESCE(j.year, c.year)
HAVING COUNT(c.articleID) >= COUNT(j.articleID)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269563

Hmmm. Let's start by getting a count for each author and year:

select a.name, year, sum(is_journal), sum(is_conference)
from ((select ja.article_id, ja.year, 1 as is_journal, 0 as is_conference
       from journalarticle ja
      ) union all
      (select ca.article_id, ca.year, 0 as is_journal, 1 as is_conference
       from conferencearticle ca
      ) 
     ) jc join
     authors a
     on a.article_id = jc.article_id
group by a.name, jc.year

Now, you can aggregate again to match the years that match the conditions:

select ay.name
from (select a.name, year, sum(is_journal) as num_journal, sum(is_conference) as num_conference
      from ((select ja.article_id, ja.year, 1 as is_journal, 0 as is_conference
             from journalarticle ja
            ) union all
            (select ca.article_id, ca.year, 0 as is_journal, 1 as is_conference
             from conferencearticle ca
            ) 
           ) jc join
           authors a
           on a.article_id = jc.article_id
      group by a.name, jc.year
     ) ay
where (jc.year >= 2000 and jc.year <= 2018) and
      num_journal >= num_conference
group by ay.name;

Upvotes: 1

Related Questions