Join problem? - 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 2000 Forums

SQL Server Development (2000)

Join problem?

Author

Topic

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2008-07-20 : 22:36:54

tblr has 1record only,but this procedure produce as much as records contained in tblb.I want records only in tblr.

CREATE PROCEDURE [dbo].[d_Report]
@status nvarchar(50)
AS
select -----
from tblr r
left join tbls s on r.sID = s.sID
left join tblc c on r.cID = c.cID
left join tblb b on convert(varchar(10), r.cID)=b.cID
where r.rs=@status
order by r.cID
GO

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-20 : 22:40:36

then use INNER JOIN instead of LEFT JOIN

KH
[spoiler]Time is always against us[/spoiler]

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2008-07-20 : 22:43:53

i used inner join previously,but can't.
So i change left join,but getting same result.
Plz tell me what's wrong?
Thanks.

quote:
Originally posted by khtan

then use INNER JOIN instead of LEFT JOIN

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-20 : 22:51:18

quote:
Originally posted by kwikwisi

i used inner join previously,but can't.
So i change left join,but getting same result.
Plz tell me what's wrong?
Thanks.

What do you mean by "CANT'T". Are you getting any error ?

Post your table DDL and sample data and the result that you want !!

KH
[spoiler]Time is always against us[/spoiler]

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2008-07-20 : 22:55:22

sorry for confusing.
I mean stored procedure can't produce records only from tblr with both inner join and left join.
I join tblb to get bdesc because I want records only from tblr.

quote:
Originally posted by khtan

quote:
Originally posted by kwikwisi

i used inner join previously,but can't.
So i change left join,but getting same result.
Plz tell me what's wrong?
Thanks.

What do you mean by "CANT'T". Are you getting any error ?

Post your table DDL and sample data and the result that you want !!

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-20 : 22:57:09

How about the DDL & sample data for these tables? ?

DDL : the create table statement . .
sample data : in form of "insert into table . . . "

KH
[spoiler]Time is always against us[/spoiler]

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2008-07-20 : 23:07:23

tblb(cid,...),tblr(cid,...)
there's only cid to relate these 2 tables.
sample data,
tblb-> cid --- ---
2
2
4
tblr->cid---- ---
2
4
there's only one record for cid 2 in tblr,but i got other records for cid 2 from tblb.

quote:
Originally posted by khtan

How about the DDL & sample data for these tables? ?

DDL : the create table statement . .
sample data : in form of "insert into table . . . "

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-20 : 23:14:06

please read http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources