Clever Geek Handbook

SQL - find all codes in a subset of years

I have a table like this:

Codes
year |  code   |
----------------
2009 | 10001   |
2009 | 10002   |
2009 | 10003   |

2010 | 10001   |
2010 | 10002   |
2010 | 10004   |

2011 | 10001   |
2011 | 10005   |
2011 | 10010   |

How to find all codes that are present in a subset of years . For example, if I want to find all codes that are present in 2009 and 2010 (and not necessarily 2011), I should get 10001

and 10002

, since they are present in 2009 and 2010. Below is incorrect, but I need something like:

SELECT code FROM Codes
GROUP BY code
HAVING year in (2009,2010)
+3


source to share


4 answers 


Select Code
 From  Codes
 Where Year In (2009,2010,2011)
 Group By Code
 Having count(Distinct Year)=3


Should only return 10001

+2


source


One way to achieve this goal is to use the operation INTERSECT 

 select code from codes where year=2009
 intersect
 select code from codes where year=2010;

This will give you:



code
10001
10002

The problem with this approach is that you will need to add INTERSECT

for each filter.

+2


source


In your example, you can find what you want with the following query:

SELECT code
FROM codes
WHERE year IN (2009,2010)
GROUP BY code
HAVING count(*)=2;

Explanation:

  • Filter only matching years
  • Grouping by codes will give you the dictate codes and, among other things, the number of lines for each group ( count(*)

    ).
  • HAVING will filter codes that have 2 occurrences. This means that they must match both years.
+2


source


your sol: -

SELECT FROM Codes WHERE year> = 2009 AND year <= 2011;

0


source






More articles:


All Articles