Selecting only authors who have articles?

Question

I've got two SQL Server tables authors, and articles where authors primary key (AuthorID) is a foreign key in the articles table to represent a simple one-to-many relationship between authors and articles table. Now here's the problem, I need to issue a full text search on the authors table based on the first name, last name, and biography columns. The full text search is working awesome and ranking and all. Now I need to add one more criteria to my search, I need all the non-articles contributors to be ignored from the search. To achieve that I chose to create a view with all the contributors that have articles and search against this view. So I created the view this way:

    Create View vw_Contributors_With_Articles
AS 
Select * from Authors
Where Authors.ContributorID 
IN ( Select Distinct (Articles.ContributorId) From Articles)

It's working but I really don't like the subquery thing. The join gets me all the redundant authorIDs, tried distinct but didn't work with the biography column as it's type is ntext. Group by wouldn't do it for me because I need all the columns not any aggregate of them.

What do you think guys? How can I improve this?

Answer 1

You want an inner join

select
  *
from
  Authors
inner join
  Articles on
  Articles.ContributorID = Authors.ContributorID

This will return only authors who have a an entry on the Articles table, matched by ContributorID.

Answer 2

An EXISTS allows for the potential duplicate entries when there are multiple articles per author:

Select * from Authors
Where EXISTS (SELECT *
    FROM Articles
    WHERE Articles.ContributorId = Authors.ContributorId)

Edit: To clarify, you can not DISTINCT on ntext columns. So, you can not have a JOIN solution, unless you use a derived table on articles in the JOIN and avoid using articles directly. Or you convert the ntext to nvarchar(max).

EXISTS or IN is your only option.

Edit 2:

...unless you really want to use a JOIN and you have SQL Server 2005 or higher, you can CAST and DISTINCT (aggregate) to avoid multiple rows in the output...

select DISTINCT
  Authors.ContributorID,
  Authors.AnotherColumn,
  CAST(Authors.biography AS nvarchar(max)) AS biography,
  Authors.YetAnotherColumn,
  ...
from
  Authors
inner join
  Articles on
  Articles.ContributorID = Authors.ContributorID

Answer 3

Select the distinct contributorIDs from the Articles table to get the individual authors who have written an article, and join the Authors table to that query - so something like

select distinct Articles.contributorID, Authors.*
from Articles
join Authors on Articles.contributerID = Authors.ContributerId