Search code examples
sql-serverquery-optimization

How to check multiple existence of Column values in SQL Server


I have a table i.e. Yearly_Author_CoAthors having structure as:

Author  |  CoAuthor  |   Year  
------------------------------
677     |  901706    |   2005
677     |  38459     |   2007
677     |  901706    |   2007
677     |  1695352   |   2007
677     |  901706    |   2009
677     |  372089    |   2011
677     |  403400    |   2011
677     |  478885    |   2011
677     |  478885    |   2012
677     |  42700     |   2013
677     |  625964    |   2013
1359    |  133112    |   2005
1359    |  1412785   |   2005
1359    |  151268    |   2006
1359    |  232222    |   2007
1359    |  264864    |   2007  
...  
...  

I have executed a query as:

SELECT * FROM Yearly_Author_CoAuthors
WHERE CoAuthor = 901706  

It gives output as:

Author  |  CoAuthor  |  Year
----------------------------
677     |  901706    |  2005
677     |  901706    |  2007
677     |  901706    |  2009
1683703 |  901706    |  2012  

It means the CoAuthor = 901706 is associated with more than 1 Author i.e. 677 and 1683703.

So the question is:
How can I modify this query to check all such CoAuthor who associated with more than 1 Author?


Solution

  • Using idea from Arulkumar, this query gave the expected result.

    SELECT   CoAuthor, COUNT(DISTINCT Author) [AuthorCount], Year
    FROM     Yearly_Author_CoAuthors
    GROUP BY Year, CoAuthor
    HAVING   COUNT(DISTINCT Author) > 1
    ORDER BY CoAuthor, Year