b174008 b174008 - 1 year ago 61
MySQL Question

MySQL excluding records without subselect to get records count

I've got the following (simplified for this question) tables in the DB:

  • article
    (id, title, text, author_id etc.)

  • publication
    (id, article_id, publication_place, date)

I want to find the number of the articles published in the last X months by specific author, but only if they were NOT published earlier anywhere.

Currently I've got:

SELECT COUNT(DISTINCT article.id) AS articlecount
FROM article
LEFT JOIN publication ON publication.article_id=article.id
WHERE author_id = :authorID
AND publication.date >= :date

...which is basically selecting articles published after
, but it doesn't exclude articles which were also published earlier. Is it possible without adding subselect (
AND article.id NOT IN ...
) for better query execution time?

Answer Source

You can do the same as the AND article.id NOT IN ... subselect with a left join:

SELECT COUNT(DISTINCT article.id) AS articlecount
FROM article a
JOIN publication p_new ON p_new.article_id=a.id
LEFT JOIN publication p_old ON p_old.article_id=a.id AND p_old.date < :date
WHERE a.author_id = :authorID
AND p_new.date >= :date
AND p_old.id IS NULL
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download