I'm trying to bind to querys and would like to know most efficient way to do it

I have these that both need binding as one

SELECT * FROM categories ORDER BY categoryDesc


select count(*) as amount from products where idCategory = "&idCat&" and listhidden = 0"

idCat is in a loop from the first query, I dont want to recordsets just the one

Many thxs

7 Years
Discussion Span
Last Post by gsc1ugs

I think this is what you want:

Select *,
  Select Count(*)
  From products (NOLOCK)
  Where products.idCategory = categories.id and products.listhidden = 0
) As Amount
From categories
Order By categoryDesc


Thxs for the first response, been trying to do count with this query but failing, can you help please?

SELECT     products.idcategory, categories.categoryDesc, products.Description, products.Details, products.ImageUrl, products.startPrice, products.idproduct, 
                      suppliers.supplierName, suppliers.imageLogo
FROM         suppliers INNER JOIN
                      categories INNER JOIN
                      products ON categories.idCategory = products.idcategory ON suppliers.idsupplier = products.idsupplier
WHERE     (products.ListHidden = 0) AND (products.idcategory = 18) AND (suppliers.idsupplier = 3)

Edited by peter_budo: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks) and [icode] (inline code) tags.


Paste the CREATE TABLE queries for the three tables involved. Your join is screwed up but I don't know how the tables relates so I can't tell you how to fix it.

Two other things:
First -- In the future please mark threads as solved when your question has been answered, then open a new thread for your new question.

Second -- Use code tags when you post code on daniweb.

[code=sql] ...query here...



Ok thxs, reduced it down and sussed problem, spiders web arghh!

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.