Joining Count Queries?

Hi,
I want to join 2 count queries toegther and can't work out how to go about
it.
The first query obtains the total products by country...
select count(*) as TotalProducts,
c.Country_Name
from product_source ps, country c where
ps.product_id in (select product_id from record_links where link_reference =
901)
and
ps.country_code = c.country_code
group by c.Country_Name
which gives...
14 Ukraine
6 Poland
1 Russia
1 Belarus
1 Lithuania
The second query obtains the countries with new products...
select count(*) as NewProducts,
c.Country_Name
from product_source ps, country c where
ps.product_id in (select product_id from record_links where link_reference =
901 and date_linked > '01/11/2005')
and
ps.country_code = c.country_code
group by c.Country_Name
which gives
1 Poland
So what I want is a single line for each country in a combined query...
14 Ukraine 0
6 Poland 1
1 Russia 0
1 Belarus 0
1 Lithuania 0
Is there a way of doing some kind of outer join to get the results i need?
thanks,
jpXref: TK2MSFTNGP01.phx.gbl microsoft.public.sqlserver.server:435994
I worked this out using a stored procedure with a sub select...
/* Country Breakdown */
CREATE PROCEDURE [dbo].[GetCountryBreakdown] AS
select count(*) as TotalProducts,
c.Country_code,
c.Country_name,
(
select count(*) as NewProducts
from product_source p where
p.product_id in (select product_id from record_links where link_reference =
901 and date_linked > '01/11/2005')
and
p.country_code = c.country_code
)
from product_source ps, country c where
ps.product_id in (select product_id from record_links where link_reference =
901)
and
ps.country_code = c.country_code
group by c.Country_Code,c.Country_name
GO
"jp" <jpfrancoisuk@.yahoo.co.uk> wrote in message
news:Zi_eg.4241$xH1.3173@.newsfe3-gui.ntli.net...
> Hi,
> I want to join 2 count queries toegther and can't work out how to go about
> it.
> The first query obtains the total products by country...
> select count(*) as TotalProducts,
> c.Country_Name
> from product_source ps, country c where
> ps.product_id in (select product_id from record_links where link_reference
> = 901)
> and
> ps.country_code = c.country_code
> group by c.Country_Name
> which gives...
> 14 Ukraine
> 6 Poland
> 1 Russia
> 1 Belarus
> 1 Lithuania
> The second query obtains the countries with new products...
> select count(*) as NewProducts,
> c.Country_Name
> from product_source ps, country c where
> ps.product_id in (select product_id from record_links where link_reference
> = 901 and date_linked > '01/11/2005')
> and
> ps.country_code = c.country_code
> group by c.Country_Name
> which gives
> 1 Poland
> So what I want is a single line for each country in a combined query...
> 14 Ukraine 0
> 6 Poland 1
> 1 Russia 0
> 1 Belarus 0
> 1 Lithuania 0
> Is there a way of doing some kind of outer join to get the results i need?
> thanks,
> jp
>