How to write a sub query with multiple conditions

nhmedia
nhmedia used Ask the Experts™
on
I have an SQL view containing many hundreds of pieces of content
And I have a table that has a record for every time those items have been viewed on a website.
I now need to extract the 20 most popular items
I have written a sub query to get the 20 most commonly viewed items:
SELECT top 20 ContentID , COUNT(ContentID) AS ID FROM  table1 (nolock) where SiteID = 26 and ContentID not in (0,-1)
 and UserAgent not like '%bot%' and  date='2011-05-16' GROUP BY  ContentID order by ID desc

But when I try something like this:
Select  * from View1 where ContentID in (SELECT top 20 ContentID , COUNT(ContentID) AS ID FROM  table1 (nolock) where SiteID = 26 and ContentID not in (0,-1)
 and UserAgent not like '%bot%' and  date='2011-05-16' GROUP BY  ContentID order by ID desc)

I get an error

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Could anyone suggest how I can structure this to query to get what I need?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Philippe DamervalSenior Analyst Programmer

Commented:
The issue is that you are trying to compare a field (ContentID) with a resultset that has two fields (ContentID, Count(ContentID).
By removing the count field from your subquery you will eliminate the error.
However, that may stop you from getting a value you need. Tell us more about what you want to do with that count.

Thanks

Philippe
Hi,
I assume you are getting the correct data for your first query.
Using that query in your second query you need to...
1. Only have 1 field for your "IN" clause to decipher.
2. Not have an "Order By" for the Statement being used for the "IN" clause

So, I think this would work..
See what it looks like...

Select  *
from View1
where ContentID in
      (SELECT top 20 ContentID FROM  table1 (nolock) where SiteID = 26 and ContentID not in (0,-1)
      and UserAgent not like '%bot%' and  date='2011-05-16' GROUP BY  ContentID)
Software Engineer
Commented:

You can take this approach
Select * 
from View1 
inner join 
  (SELECT top 20 ContentID , COUNT(ContentID) AS ID 
   FROM  table1 (nolock) 
   where SiteID = 26 and 
   ContentID not in (0,-1)
   and UserAgent not like '%bot%' 
   and  date='2011-05-16' 
   GROUP BY ContentID 
   order by ID desc) MostPopular on  MostPopular.ContentID = View1.ContentID

Open in new window

Author

Commented:
Thanks Phillipe

You are correct, I need the count field because i have to sort the records so I have the most viewed first.

I was then trying to isolate the contentID field - and that's where it was all going wrong.

KR

GG

Author

Commented:
I just  ran this and it worked perfectly. Thank you very much!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial