Join, Group by: can't have it right

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

Transact-SQL (2000)

Join, Group by: can't have it right

Author

Topic

Corobori
Posting Yak Master

105 Posts

Posted - 2002-03-01 : 09:06:00

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 ?

nr
SQLTeam MVY

12543 Posts

Posted - 2002-03-01 : 11:01:45

Instead of the where clause and a null check you could

SELECT City_Id, City_Name, iMonth, iYear, iNoNews
FROM tblCity
left outer join vwNewsPerCity
ON vwNewsPerCity. News_City =tblCity.City_Id
and vwNewsPerCity.iMonth = 2
and vwNewsPerCity.iYear = 2002

If you only want the cities which have news in the month/year then just change the outer join to an inner join.

Instead of month and year you could have instead or in addition a datatime as the first of the month to save a bit of work.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

Corobori
Posting Yak Master

105 Posts

Posted - 2002-03-01 : 13:09:56

Just try the following and you'll see more easily where is 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

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-03-02 : 07:23:21

Suspect that
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)

should be
CREATE VIEW dbo.vwCity
AS
SELECT dbo.City.City_Id, dbo.City.City_Name, dbo.vwNewsPerCity.iYear, dbo.vwNewsPerCity.iMonth
FROM dbo.City LEFT 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)

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources