Having trouble using IN - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Having trouble using IN

Author

Topic

toddhd
Yak Posting Veteran

55 Posts

Posted - 2005-05-25 : 15:04:56

I have a bunch of tables related to a document management system. The main table is called KBArticles, and it includes the KBArticleID (uniqueid), ArticleID, Title and Content of the articles. All the other tables are related to properties of each article (not really important to this matter). The KBArticles table is Full Text Indexed using SQL 2000. I wrote a SELECT query to pull the records sorted by rank. It looks like this, and works fine:

SELECT DISTINCT TOP 100 PERCENT KEY_TBL.RANK, a.ArticleID, a.KBArticleID, a.Title
FROM KBArticles AS a
INNER JOIN FREETEXTTABLE(KBArticles, *, 'hr') AS KEY_TBL ON a.KBArticleID = KEY_TBL.[KEY]
LEFT OUTER JOIN KBArticleProducts ON a.ArticleID = KBArticleProducts.ArticleID
LEFT OUTER JOIN KBProducts ON KBArticleProducts.ProductID = KBProducts.ProductID
LEFT OUTER JOIN KBArticleVersions ON a.ArticleID = KBArticleVersions.ArticleID
LEFT OUTER JOIN KBProductVersions ON KBArticleVersions.VersionID = KBProductVersions.VersionID
LEFT OUTER JOIN KBArticleCategories ON a.ArticleID = KBArticleCategories.ArticleID
LEFT OUTER JOIN KBProductCategories ON KBArticleCategories.CategoryID = KBProductCategories.CategoryID
LEFT OUTER JOIN KBArticleSubCategories ON a.ArticleID = KBArticleSubCategories.ArticleID
LEFT OUTER JOIN KBProductSubCategories ON KBArticleSubCategories.SubCategoryID = KBProductSubCategories.SubCategoryID
LEFT OUTER JOIN KBArticleTypes ON a.ArticleID = KBArticleTypes.ArticleID
LEFT OUTER JOIN KBTypes ON KBArticleTypes.TypeID = KBTypes.TypeID
LEFT OUTER JOIN KBArticleLocales ON a.ArticleID = KBArticleLocales.ArticleID
LEFT OUTER JOIN KBLocales ON KBArticleLocales.LocaleID = KBLocales.LocaleID
LEFT OUTER JOIN KBArticleRoles ON a.ArticleID = KBArticleRoles.ArticleID
LEFT OUTER JOIN Roles ON KBArticleRoles.RoleID = Roles.RoleID
WHERE a.Enabled = 1
ORDER BY KEY_TBL.RANK DESC

HERE IS THE PROBLEM. I need to extract the KBArticles.Content field as well. But since DISTINCT won't work with Text fields, I can't do that directly. So I decided to just wrap the above as a subquery, and then pull the relevent records, like this:

SELECT Content FROM KBArticles WHERE KBArticleID IN
(SELECT DISTINCT TOP 100 PERCENT KEY_TBL.RANK, a.ArticleID, a.KBArticleID, a.Title
FROM KBArticles AS a
INNER JOIN FREETEXTTABLE(KBArticles, *, 'hr') AS KEY_TBL ON a.KBArticleID = KEY_TBL.[KEY]
LEFT OUTER JOIN KBArticleProducts ON a.ArticleID = KBArticleProducts.ArticleID
LEFT OUTER JOIN KBProducts ON KBArticleProducts.ProductID = KBProducts.ProductID
LEFT OUTER JOIN KBArticleVersions ON a.ArticleID = KBArticleVersions.ArticleID
LEFT OUTER JOIN KBProductVersions ON KBArticleVersions.VersionID = KBProductVersions.VersionID
LEFT OUTER JOIN KBArticleCategories ON a.ArticleID = KBArticleCategories.ArticleID
LEFT OUTER JOIN KBProductCategories ON KBArticleCategories.CategoryID = KBProductCategories.CategoryID
LEFT OUTER JOIN KBArticleSubCategories ON a.ArticleID = KBArticleSubCategories.ArticleID
LEFT OUTER JOIN KBProductSubCategories ON KBArticleSubCategories.SubCategoryID = KBProductSubCategories.SubCategoryID
LEFT OUTER JOIN KBArticleTypes ON a.ArticleID = KBArticleTypes.ArticleID
LEFT OUTER JOIN KBTypes ON KBArticleTypes.TypeID = KBTypes.TypeID
LEFT OUTER JOIN KBArticleLocales ON a.ArticleID = KBArticleLocales.ArticleID
LEFT OUTER JOIN KBLocales ON KBArticleLocales.LocaleID = KBLocales.LocaleID
LEFT OUTER JOIN KBArticleRoles ON a.ArticleID = KBArticleRoles.ArticleID
LEFT OUTER JOIN Roles ON KBArticleRoles.RoleID = Roles.RoleID
WHERE a.Enabled = 1
ORDER BY KEY_TBL.RANK DESC)

Makes sense, right? But I get this error message:

Server: Msg 116, Level 16, State 1, Line 1
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Why am I getting this message? I tried replacing the IN statement with EXISTS, but that just gives me dozens of records (it should have given me three).

Am I using IN wrongly? Any suggstions?

SreenivasBora
Posting Yak Master

164 Posts

Posted - 2005-05-25 : 15:18:49

Yes You cant refer Multiple columns for IN Clause. So you have to use EXISTS Clause.

Here is the Mistake:
===================

SELECT Content FROM KBArticles WHERE KBArticleID IN
(SELECT DISTINCT TOP 100 PERCENT KEY_TBL.RANK, a.ArticleID, a.KBArticleID, a.Title
FROM KBArticles AS a

You can write like this:
========================

SELECT Content FROM KBArticles WHERE KBArticleID IN
(SELECT DISTINCT TOP 100 PERCENT a.ArticleID FROM KBArticles AS a

If you need more columns then:
==============================

SELECT Content FROM KBArticles WHERE KBArticleID EXISTS
(SELECT DISTINCT TOP 100 PERCENT KEY_TBL.RANK, a.ArticleID, a.KBArticleID, a.Title
FROM KBArticles AS a

With Regards
Sreenivas Reddy B

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources