SQL Select from same table

Question

I have a table like this:

I would like to write a query that outputs the records that have occurencies for both years 2000 and 2001 only.

In this case, the output would be

Bb 2000
Bb 2001

because only the records with the name Bb have occurencies on both years.

I have tried Subqueries and Joins but haven't had success, since the two columns are not Primary Keys nor Foreign Keys.

Thank you.

Answer 1

My table can only have 2000 or 2001 in the year column. No other year will appear. However, if it was possible to have other values for year then I would want to get the records that had more than one year for the same name.

If you ever have the need to deal with other years as described in your comment, then this is the query I would use:

select id, name, year
  from (select id, name, year,
               count(distinct year) over (partition by name) as distinct_year_cnt
          from table_name)
 where distinct_year_cnt > 1

Answer 2

You can use intersect to do this.

select x.name, x.year from
(
select name from table where year = 2000
intersect
select name from table where year = 2001
) t join table x on t.name = x.name