Using a nested SQL query to select items in a specific subset and with a count>1-CodePudding

I am trying to figure out how to select data from two tables where I select data with count>1 and only in a specific year. My current query is as follows:

SELECT table1.ID, table1.name, table1.data
FROM table1
WHERE  table1.ID IN (
    SELECT table2.ID
    FROM table2
    WHERE table2.year = '2020' AND table2.ID IN (
        SELECT table2.ID
        FROM table2
        GROUP BY table2.ID
        HAVING COUNT(*) > 1
    )
);

However this retrieves data with count=1 in the given year if count>1 across multiple years. How do I write the query to exclude these?

Edit background: In table1 which I want output from I we have names and data related to the key ID this table does not change frequently, in table2 I am storing information updated yearly. I just want to select the id, name and data for those which occur in 2020 more than once, but not occurring more than once across multiple years.

CodePudding user response：

You could express this in as a join:

SELECT t1.ID, t1.name, t1.data
FROM table1 t1
INNER JOIN
(
    SELECT ID
    FROM table2
    GROUP BY ID
    HAVING COUNT(CASE WHEN year = 2020 THEN 1 END) > 0 AND COUNT(*) > 1
) t2
    ON t2.ID = t1.ID;

CodePudding user response：

You should check both conditions in your IN clause:

Select table1.ID, table1.name, table1.data
From table1
WHERE  table1.ID IN (
    SELECT table2.ID
    FROM table2
    WHERE table2.ID IN (
        SELECT table2.ID
        FROM table2
        GROUP BY table2.ID
        HAVING COUNT(*) > 1 and
        table2.year = '2020')
);