SQL Query for accessing information from two separate tables-CodePudding

I have two tables named Articles and Publishers

In Article table, there are multiple columns but there are 3 of note: ArticleId, PublishDate, and AuthorName.

In the Publishers table, there are also multiple columns but 2 of note: ArticleId and HasPublisher.

So for example if the Article table contains:

ArticleID   PublishDate  AuthorName
1              9/27/21     John
2              12/13/21   Smith
3               1/3/22.    Bob

and the Publisher table contains

ArticleID   HasPublisher
 1             Yes
 2             No
 3              Yes

What query will I have to run to check how many rows in the Publisher table has column HasPublisher = yes in which the same Id in the Article table has a publishDate column value of past date X.

So in this example if we want to fit criteria of date being past 9/26/21, the result would be 2 (Id = 1 and Id = 3 are value HasPublisher = Yes in the second table) but if we wanted to fit critera of date being past 1/1/22, the result would only be 1 entry (Id = 3).

CodePudding user response：

select COUNT(p.*)
from Article a
inner join Publisher p on p.ArticleID = a.ArticleID
where p.HasPublisher = 'Yes' AND a.PublishDate > '2021-09-26'

Note how I formatted the date literal. If you're actually storing the dates as varchar columns in the 9/27/21 format, you're doing it wrong. The schema itself is broken, and you need to fix it. It is still possible to write this query with the broken schema, but the fixed version can take advantage of an index to run multiple orders of magnitude faster, and I promise you will save you much heartache in the future.

CodePudding user response：

SELECT COUNT(1) FROM Article art
INNER JOIN Publisher pub
ON  art.ArticleID   = pub.ArticleID 
WHERE PublishDate  > '2021-07-26'
AND HasPublisher = 'Yes'

You can inner join both the table on the ArticleId column and then filter on the criteria of HasPublisher and PublishDate.

Edit: Corrected the date format