need help with query - 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)

need help with query

Author

Topic

tocroi72
Yak Posting Veteran

89 Posts

Posted - 2007-08-03 : 10:39:10

Hello all,

I have 3 tables below:

create table Book(bookid int,title varchar(50))
insert into BOOK
select 1,'mssql' unition all select 2 ,'c++' union all
select 3,'Visual Basic' union all select 4,'Java'

----------------
create table BookDiscount(discountid int,bookid int)
insert into BookDiscount select 1,1 union all select 2,3

------------------
create table BookDiscountSequence(discountid int,bookid int,Sequenceid int,Sequencedesc varchar(50))
insert into BookDiscountSequence select 1,1,1,'Christmas' union all select 1,1,2,'newyear'
-------------

I ran a query below:
select a.*,b.*,
Occasion1 =
case
when c.sequenceid = 1 then Sequencedesc
end
,
Occasion2 =
case
when c.sequenceid = 2 then SequenceDesc
end
from book a, BookDiscount b ,BookDiscountSequence c
where a.bookid = b.bookid
and a.bookid = c.bookid
and b.bookid = c.bookid
-----------------
the result is below:
1 mssql 1 1 Christmas NULL
1 mssql 1 1 NULL newyear

how do i get the result just as below:
1 mssql 1 1 Christmas newyear
--------------

any helps will be greatly appreciated.

Thanks

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-08-03 : 11:01:22

Here's one way:

create table #Book(bookid int,title varchar(50))
insert into #BOOK
select 1,'mssql' union all select 2 ,'c++' union all
select 3,'Visual Basic' union all select 4,'Java'

----------------
create table #BookDiscount(discountid int,bookid int)
insert into #BookDiscount select 1,1 union all select 2,3

------------------
create table #BookDiscountSequence(discountid int,bookid int,Sequenceid int,Sequencedesc varchar(50))
insert into #BookDiscountSequence select 1,1,1,'Christmas' union all select 1,1,2,'newyear'
-------------

select a.bookid,a.title,b.discountid,b.bookid,
Occasion1 = MAX(
case
when c.sequenceid = 1 then c.Sequencedesc else ''
end)
,
Occasion2 = MAX(
case
when c.sequenceid = 2 then c.SequenceDesc else ''
end)
from #book a, #BookDiscount b ,#BookDiscountSequence c
where a.bookid = b.bookid
and a.bookid = c.bookid
and b.bookid = c.bookid

GROUP BY a.bookid,a.title,b.discountid,b.bookid

Jim

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources