February 28, 2002 at 5:03 pm
Say a table of news (tblNews) like this
News_Id
News_City
News_Date
And a table of Cities (tblCity)
City_Id
City_Name
I created a view (vwNewsPerCity) where I am counting the number of new per
city/year/month
SELECT TOP 100 PERCENT COUNT(News_Id) AS iNoNews, YEAR(News_Date) AS iYear, MONTH(News_Date) AS iMonth, News_City
FROM dbo.tblNoticias
GROUP BY YEAR(News_Date), MONTH(News_Date), News_City
Now I would like a query returning me for a particular year and month for each cities the number of news and just the name of the cities when there is no news
I wrote this view:
SELECT City_Id, City_Name, iMonth, iYear, iNoNews
FROM vwNewsPerCity RIGHT OUTER JOIN
tblCity ON vwNewsPerCity. News_City =tblCity.City_Id
WHERE (vwNewsPerCity.iMonth = 2) AND (vwNewsPerCity.iYear = 2002) OR
(vwNewsPerCity.iMonth IS NULL) AND (vwNewsPerCity.iYear IS NULL)
Well, this does not work the way I want because in this case it displays all cities which have news or not in the year 2002 but not the cities which are having news only in 2001
So my question how is the right way to perform this ?
Jean-Luc
Jean-Luc
www.corobori.com
February 28, 2002 at 7:32 pm
By putting criteria in the where clause you essentially are changing the join to an inner join. Not sure if this is what you want, but maybe!
SELECT City_Id, City_Name, iMonth, iYear, iNoNews
FROM vNewsPerCity RIGHT OUTER JOIN
Cities ON vNewsPerCity.News_City = Cities.City_Id and (vNewsPerCity.iMonth = 2) AND (vNewsPerCity.iYear = 2002) OR (vNewsPerCity.iMonth IS NULL) AND (vNewsPerCity.iYear IS NULL)
Returns:
1Orlando220021
2MiamiNULLNULLNULL
3TampaNULLNULLNULL
4DenverNULLNULLNULL
Andy
March 1, 2002 at 9:38 am
I woud guess that is why there are the two 'is null' checks - to preserve the outer join.
Could get rid of them and include the where clause in the outer join.
But the question sounds like it wants an inner join - so just omit the null checks and change to inner join.
Cursors never.
DTS - only when needed and never to control.
March 1, 2002 at 11:29 am
Just try the following code and you'll see my problem
create table News
(
News_Id int,
News_City int,
News_Date DateTime
primary key (News_Id, News_City)
)
go
create table City
(
City_Id int,
City_Name varchar(20)
primary key (City_Id)
)
go
insert into News values (1, 1, '2002-01-02')
insert into News values (2, 2, '2002-01-02')
insert into News values (3, 1, '2001-31-12')
insert into City values (1, 'Adelaide')
insert into City values (2, 'Melbourne')
insert into City values (3, 'Sydney')
go
CREATE VIEW dbo.vwNewsPerCity
AS
SELECT COUNT(News_Id) AS EXPR1, News_City, YEAR(News_Date) AS iYear, MONTH(News_Date) AS iMonth
FROM dbo.News
GROUP BY News_City, YEAR(News_Date), MONTH(News_Date)
go
CREATE VIEW dbo.vwCity
AS
SELECT dbo.City.City_Id, dbo.City.City_Name, dbo.vwNewsPerCity.iYear, dbo.vwNewsPerCity.iMonth
FROM dbo.City INNER JOIN
dbo.vwNewsPerCity ON dbo.City.City_Id = dbo.vwNewsPerCity.News_City
WHERE (dbo.vwNewsPerCity.iYear = 2002) AND (dbo.vwNewsPerCity.iMonth = 2) OR
(dbo.vwNewsPerCity.iYear IS NULL) AND (dbo.vwNewsPerCity.iMonth IS NULL)
go
-- In this query only 2 cities will show up (Adelaide and Melbourne) whereas I am expecting 3 (Sydney is missing)
select * from vwCity
Jean-Luc
Jean-Luc
www.corobori.com
March 1, 2002 at 1:00 pm
Does this work for you
SELECT City.City_Id, City.City_Name,
News.iYear, News.iMonth , News.NewsCount
FROM City
LEFT OUTER JOIN
(
SELECT News_City,
COUNT(News_Id) As NewsCount,
YEAR(News_Date) AS iYear,
MONTH(News_Date) AS iMonth
FROM News
WHERE (YEAR(News_Date) = 2002) AND
(MONTH(News_Date) = 1)
GROUP BY News_City, YEAR(News_Date), MONTH(News_Date)
) As News ON City.City_id = News.News_City
March 1, 2002 at 1:35 pm
It looks like its working.
Thanks
Jean-Luc
Jean-Luc
www.corobori.com
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply