Bind to querys

[gsc1ugs]

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

and

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

[sknake]

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

[gsc1ugs]

Your the man, brilliant works great

[gsc1ugs]

Hi

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)

[sknake]

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...

[/code]

[gsc1ugs]

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