Full outer join - 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 2005 Forums

Transact-SQL (2005)

Full outer join

Author

Topic

IBoonZ
Yak Posting Veteran

53 Posts

Posted - 2009-11-24 : 17:03:09

hi guys :)

I've been cracking my head on this problem, and i cant find the answer.

Following question:
I want to see the count of books who have been published by every publisher in every categorie, AND i also need to see the zero's.
(I mean also the names of the publisher who dident gave any book and the names of the categories where the publisher dident publish them)
DAMN Crazy ****

This is what i tried, but i dont get the zeros -->

select uitgever, categorie, count(*)as [aantal boeken] from boeken
full outer join categorie
on categorie.cat_id = boeken.cat_id
full outer join uitgever
on uitgever.uitg_id = boeken.uitg_id
group by categorie, uitgever

IBoonZ
Yak Posting Veteran

53 Posts

Posted - 2009-11-24 : 17:04:44

[url]http://img137.imageshack.us/img137/3618/sql.png[/url]

Better image res :)

kbhere
Yak Posting Veteran

58 Posts

Posted - 2009-11-25 : 00:14:47

Kindly post your table structure with sample data..
So, it will be easy to analyse the problem and get a good solution for your need..

Balaji.K

Sachin.Nand

2937 Posts

Posted - 2009-11-25 : 01:18:07

I am not sure if this gives u the expected result
declare @books as table(catid int,pubid int,title varchar(50))
insert into @books
select 2,4,'a' union all
select 2,5,'b' union all
select 2,6,'c' union all
select 1,6,'d' union all
select 2,6,'e'

declare @publishers as table(pubid int,pubname varchar(50))
insert into @publishers
select 1,'a1' union all
select 4,'a2' union all
select 5,'a3' union all
select 6,'a4'

declare @categories as table(catid int,catname varchar(50))
insert into @categories
select 1,'cat1' union all
select 2,'cat2' union all
select 3,'cat3'

select p.pubid,p.pubname,b.catid,c.catname,
count( b.pubid)over(partition by b.pubid) as pubcount,
count( b.catid)over(partition by b.catid) as catcount
from @books b
right outer join @categories c
on c.catid = b.catid
right join @publishers p
on p.pubid = b.pubid
group by p.pubid,b.catid,b.pubid,p.pubname,c.catname

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources