[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 279
  • Last Modified:

SQL Query help for ASP code

In this query, I am trying to get the top 5 shops in the table based on the products liked to them.
It works well until I try to query the Products table to see if the product is active.

          strsqlPC = "SELECT top 5 shopid,Count(*) as numCount FROM Shop2Products WHERE productid=(SELECT productID from Products WHERE prodActive='Active') GROUP BY shopid ORDER BY Count(*) DESC"


What I get is:
Microsoft JET Database Engine error '80004005'
At most one record can be returned by this subquery.

I am running ASP code which is querying an Access Database.
0
JuniorBee
Asked:
JuniorBee
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
      strsqlPC = "SELECT top 5 shopid,Count(*) as numCount FROM Shop2Products WHERE productid IN (SELECT productID from Products WHERE prodActive='Active') GROUP BY shopid ORDER BY Count(*) DESC"
0
 
JuniorBeeAuthor Commented:
Brilliant angelIII.  Thanks!!!
=)
0
 
JuniorBeeAuthor Commented:
Learned something new.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now