COUNT() / Subquery? - 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)

COUNT() / Subquery?

Author

Topic

AdamKosecki
Starting Member

18 Posts

Posted - 2006-01-03 : 19:11:21

Ok,

Using the following query I get some wonderful data:

SELECT s.subscriptionid, p.description, s.cdate, s.suspended, s.duration, s.startdate,
s.maxtickets
FROM subscription_customer_J scj
INNER JOIN subscriptions s
ON s.subscriptionid = scj.subscriptionid
INNER JOIN products p
ON p.product_id = s.productid
WHERE scj.customerid = '#customerid#'

For each record that returns there will be a unique "subscriptionid" which I can use in this query inside a loop when I print out each row:

SELECT COUNT(t.ticketid) AS ticketCount
FROM subscription_ticket_J j
INNER JOIN tickets t
ON t.ticketid = j.ticketid
WHERE j.subscriptionid = #subscriptionid# AND t.defect = 1

To get the number of tickets attached to each subscription. I don't want to have a query inside a loop though. Could someone please give me a hint on how to combine the two queries?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-03 : 21:07:56

Is this what you want ?

SELECT s.subscriptionid, p.description, s.cdate, s.suspended, s.duration, s.startdate, s.maxtickets,
      (SELECT COUNT(t.ticketid)
       FROM subscription_ticket_J j
       INNER JOIN tickets t
       ON t.ticketid = j.ticketid
       WHERE j.subscriptionid = s.subscriptionid AND t.defect = 1) AS ticketCount
FROM subscription_customer_J scj
INNER JOIN subscriptions s
ON s.subscriptionid = scj.subscriptionid
INNER JOIN products p
ON p.product_id = s.productid
WHERE scj.customerid = '#customerid#'

-----------------
[KH]
2006 a new beginning