May 5, 2010 at 1:08 pm
i have three tables
Table A:opportunity
structure:
oppId int,
asc varchar(20)
Table B:Book table
Oppid int,
booked int,
year int
table c:sales table
oppid int,
shippped int.
year int
table A insertion:
insert into opportunity values
(1,'xxx')
(2,'ghj')
(3,'ghj')
(4,'ghj')
(5,'ghj')
table b insertion:
insert into Book values
(1,100,2010)
(2,300,2010)
table c insertion:
insert into sales values
(2,200,2010)
(3,300,2010)
now i need all the oppid's which are booked and shipped:
something like this
opp id booked shipped
1 100 null
2 null 200
3 300 300
4 null null
5 null null
If i do left outer join i should get it but i am not getting the results i am trying to do find sum (booked) and sum(shipped)
which should be 400 and 500 .
select
sum(coalesce(B.shipped,0))
,sum(coalesce(B.booked,0))
from
tOpportunity O
left outer join tSalesS
on S.OppID = O.OppID
left outer join tBook B
on B.OppID = O.OID
where
B.year =2010
and S.year =2010
I am not getting the results as i showed you ,can any one tell me why i am not getting values using simple left outer join.
May 5, 2010 at 1:25 pm
Because your WHERE clause is essentially turning the OUTER JOINs into INNER JOINs. Move the criteria from the WHERE clause to the appropriate ON clause in the OUTER JOINS.
And drop the WHERE clause after you do that.
May 5, 2010 at 1:27 pm
I got it ,left outer join is acting like an inner join here so i have changed my query to this:
select
sum(coalesce(B.shipped,0))
,sum(coalesce(B.booked,0))
from
tOpportunity O
left outer join tSalesS
on S.OppID = O.OppID
and S.year =2010
left outer join tBook B
on B.OppID = O.OID
and B.year =2010
May 5, 2010 at 1:32 pm
Looks about what I came up with, just not formatted the same.
Does this query now return your expected results?
May 5, 2010 at 1:46 pm
yes lynn,it is giving me like this:
opp id booked shipped
1 100 null
2 null 200
3 300 300
4 null null
5 null null
and when i do sum of booked and shipped it is giving me 400 and 500
Thanks for the reply Lynn.
May 5, 2010 at 2:08 pm
Glad it is working. Thanks for the feedback.
May 6, 2010 at 7:00 am
Now, i need the out put in a different way which i will show you after i insert some more data into Book and sales tables.
insert into opportunity values
(1,'xxx')
(2,'ghj')
(3,'ghj')
(4,'ghj')
(5,'ghj')
table b insertion:
insert into Book values
(1,100,2010)
(2,300,2010)
(3,500,2009)
(5,300,2008)
table c insertion:
insert into sales values
(2,200,2010)
(3,300,2010)
(5,300,2010)
Now i want something like this:
Year booked shipped
2008 300 null
2009 500 null
2010 400 600
I need to have year column as i showed above but couldn't find the way to get it and this is what i have tried:
and this is what i am getting:
b.Year s.Year booked shipped
2008 null 300 null
2009 null 500 null
2010 2010 400 600
select
sum(coalesce(B.shipped,0))
,sum(coalesce(B.booked,0))
from
tOpportunity O
left outer join tSalesS
on S.OppID = O.OppID
and S.year between 2008 and 2010
left outer join tBook B
on B.OppID = O.OID
and B.year between 2008 and 2010
I do have a Date table i don't know how to use that table:
Date table structure:
Column Datatype
Day Datetime,
month int,
year int,
insert into Date Values
(2008-01-01,1,2008)
(2009-01-01,1,2009)
(2010-01-01,1,2010)
I don't know if i need to use these table or not to get the Year Column.
Can anyone help me..................
May 6, 2010 at 7:37 am
From your post:
Now i want something like this:
Year booked shipped
2008 300 null
2009 500 null
2010 400 600
I need to have year column as i showed above but couldn't find the way to get it and this is what i have tried:
and this is what i am getting:
b.Year s.Year booked shipped
2008 null 300 null
2009 null 500 null
2010 2010 400 600
Look real close at the the output you are getting agaist the output you want. What i see is one extra column, s.Year, that if you drop that from your SELECT will give you what you are looking for from your query.
May 6, 2010 at 7:51 am
What if i have data in sales table like this:
table c insertion:
insert into sales values
(2,200,2007)
(3,300,2010)
(5,300,2010)
if i only include B.year then i will miss 2007 shipped data in Sales table right.
May 7, 2010 at 9:27 am
can anyone please answer my previous reply ,its urgent.
May 7, 2010 at 9:32 am
srilu_bannu (5/6/2010)
What if i have data in sales table like this:table c insertion:
insert into sales values
(2,200,2007)
(3,300,2010)
(5,300,2010)
if i only include B.year then i will miss 2007 shipped data in Sales table right.
Is this still how you have this portion of your query written?
left outer join tSalesS
on S.OppID = O.OppID
and S.year between 2008 and 2010
left outer join tBook B
on B.OppID = O.OID
and B.year between 2008 and 2010
If so, it is obvious to me why you miss 2007. Do you see why?
May 7, 2010 at 9:37 am
of course i will change it to 2007 to 2010 ,but how will i get the desired column.if i include s.year i will miss 2008 and 2009 ,or if i include b.year i will miss 2007.
Year
2007
2008
2009
2010
May 7, 2010 at 9:53 am
If you want additional help you need to do some additional work.
You need to post the DDL (CREATE TABLE statement(s)) for the table(s) involved. You need to report the sample data so that it works in SQL Server 2005. Please test your scripts before you post them to be sure they work.
May 7, 2010 at 2:47 pm
I did test these scripts and they are working in sql server 2005.
create table OPPortunity
(
oppId int,
abc varchar(20)
)
create table book
(
Oppid int,
booked int,
year int
)
create table sales
(
oppid int,
shippped int,
year int
)
--inserting into opportunity table
insert into opportunity values (1,'xxx')
insert into opportunity values (2,'ghj')
insert into opportunity values (3,'ghj')
insert into opportunity values (4,'ghj')
insert into opportunity values (5,'ghj')
--inserting into Book table
insert into Book values (1,100,2010)
insert into Book values (2,300,2010)
insert into Book values (3,500,2009)
insert into Book values (5,300,2008)
--inserting into sales table
insert into sales values (2,200,2007)
insert into sales values (3,300,2010)
insert into sales values (5,300,2010)
select * from book
select * from sales
select * from opportunity
-- when i execute this query i got the below results:
select
b.year,
s.year,
sum(s.shippped) as shipped
,sum(B.booked) as booked
from
Opportunity O
left outer join Sales S
on S.OppID = O.OppID
and S.year between 2007 and 2010
left outer join Book B
on B.OppID = O.OppID
and B.year between 2007 and 2010
group by b.year,s.year
-- Results:
year year shipped booked
NULL NULL NULL NULL
2010 NULL NULL 100
2010 2007 200 300
2008 2010 300 300
2009 2010 300 500
But i need the output like this:
Year Booked Shipped
2007 null 200
2008 300 null
2009 500 null
2010 400 500
I need sum (booked ) and sum(Shipped) for the total year column as shown above.
May 7, 2010 at 4:23 pm
Just an FYI, your code will not work on my systems without modification. Our servers are configured to use case sensitive collation. You really need to be sure that your code is consistant. For example, on my system the column Oppid is not the same as OppID.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply