Subqueries

[Morgs Morgan]

Hi guys, I have three different queries and i would like to put them as one huge query. This will reduce the number of times i make a connection to Sql Server and hence load time on my page. I have already managed to put two queries like:

SELECT COUNT(Cellsmart_ussd.dbo.ussd_log.[msisdn]) AS totentries,
(
SELECT COUNT(ussd_clean.dbo.ster_vouchers.sent) FROM ussd_clean.dbo.ster_vouchers
WHERE [sent] = '1' AND [sent_date] BETWEEN @start AND @end
) AS [totsent]
FROM [Cellsmart_ussd].[dbo].[ussd_log]
WHERE [request] = '*120*789#'

But i also want the query below to be part of the two subqueries above^:

SELECT COUNT([ussd_clean].[dbo].[unique_codes].[msisdn_pair]) AS tota FROM [ussd_clean].[dbo].[unique_codes]
WHERE [msisdn_pair] != '' AND [used] = '1'

Unfortunatly I can't use INNER JOINS because all these single queries refer to different databases and tables. Please help me :doh: Thanks, Morgs

[Lost User]

You can join all the three queries as following.

SELECT COUNT(Cellsmart_ussd.dbo.ussd_log.[msisdn]) AS totentries,

(
SELECT COUNT(ussd_clean.dbo.ster_vouchers.sent) FROM ussd_clean.dbo.ster_vouchers
WHERE [sent] = '1' AND [sent_date] BETWEEN @start AND @end
) AS [totsent],

(
SELECT COUNT([ussd_clean].[dbo].[unique_codes].[msisdn_pair]) FROM [ussd_clean].[dbo].[unique_codes]
WHERE [msisdn_pair] != '' AND [used] = '1'
) AS tota

FROM [Cellsmart_ussd].[dbo].[ussd_log]
WHERE [request] = '*120*789#'

It is in the same manner as you joined the first two query. Also, please post database related question in General Database[^] section. HTH

Jinal Desai - LIVE Experience is mother of sage....

[Morgs Morgan]

hey thanks Jinal, that code worked and am so impressed now...thanks again