April 18, 2012 at 11:08 am
view1
id bigint
id2 int
iddesc char(25)
table1
id
id2
amount
count
date
select * from table1,
left join view1 on table1.id -view1.id
and table1.id2=view1.id2
where id2=?
and (date=? or date is null)
The join always comes up as inner join. I get data that match the criteria only.
Please suggest
Thanks,
April 18, 2012 at 11:13 am
Not enough information to really help.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ and follow the instructions on what information to post and how to post it. The more information you provide, the better answers you will get.
April 18, 2012 at 11:40 am
sample data:
Create table view1(
id bigint
id2 int
iddesc char(25)
);
Create table table1(
id bigint,
id2 int,
amount money,
count int,
date date
);
-- view1 data
insert into view1(id, id2,iddesc) values( 1,100,'value1');
insert into view1(id, id2,iddesc) values( 2,100,'token');
insert into view1(id, id2,iddesc) values( 3,100,'dollarAMt');
insert into view1(id, id2,iddesc) values( 4,100,'discount');
insert into view1(id, id2,iddesc) values( 5,100,'lunch');
insert into view1(id, id2,iddesc) values( 6,100, 'daily');
insert into view1(id, id2,iddesc) values( 7,100, 'monthly');
insert into view1(id, id2,iddesc) values( 8,100, 'weekly');
-- table1 data
insert into table1(id, id2, amount, count, date) values(1, 100, 500.00 , 43, '2012-01-01');
insert into table1(id, id2, amount, count, date) values(3, 100, 5030.00 , 423, '2012-01-01');
insert into table1(id, id2, amount, count, date) values(5, 100, 235.00 , 44, '2012-01-01');
insert into table1(id, id2, amount, count, date) values(8, 100, 1245.00 , 4, '2012-01-01');
with the outer join, I need all data for id and id1 and matching data from table1 for a particular date
id id1 iddesc amount count
1 100 value1 500 43
2 100 token 5030 423
3 100
4 100
5 100 dollarAmr 235 44
6 100
7 100
8 100 weekly 1245 4
April 18, 2012 at 11:46 am
Your join was wrong. I flipped the view and table and it looks like it works based on the sample data provided.
select
*
from
view1
left join table1
on table1.id = view1.id
and table1.id2=view1.id2
where
VIEW1.id2=100
and (date='2012-01-01' or date is null)
April 18, 2012 at 11:48 am
ok you have your join backwards. if you have the tables set like so in your query: table1 view1. now a left join will give you every thing from table 1 and only matching from view1. you need to use a right join or switch your tables around (to make the left join work correctly.)
select * from table1
RIGHT join view1 on table1.id = view1.id
and table1.id2=view1.id2
where id2=100
and (date='2012-01-01' or date is null)
EDIT beaten to it. i went with the right join in my code. both will work.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply