problem in using left 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 2000 Forums

Transact-SQL (2000)

problem in using left join

Author

Topic

acdacd
Yak Posting Veteran

63 Posts

Posted - 2005-08-19 : 03:58:52

TableA left Join TableB

There exist some row that TableA record exist but col of tableB is null

My problem is

"When the col of table is null, that's fine
But when col of table is not null, i add some condition (ie where TableB.col1 = 'ABC')"

How to do this "if-like" condition?

be a hardworking people!!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-19 : 04:09:33

Post table structures, sample and expected data

Madhivanan

Failing to plan is Planning to fail

acdacd
Yak Posting Veteran

63 Posts

Posted - 2005-08-19 : 04:37:22

CREATE TABLE [dbo].[TestTable2] (
[ID2] [int] IDENTITY (1, 1) NOT NULL ,
[Info3] [int] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[TestTable3] (
[ID3] [int] IDENTITY (1, 1) NOT NULL ,
[Info3ref] [int] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[TestTable2] ADD
CONSTRAINT [PK_TestTable2] PRIMARY KEY CLUSTERED
(
[ID2]
) ON [PRIMARY]
GO
/*******Data ***/
TestTable2
<ID2..Info3 >
9 ....89
10 ...998

TestTable3
<ID2...Info3ref>
1.......9
2........9

/*****Orginal Query *********/
select T2.ID2, T3.ID3 from
TestTable2 T2 left join TestTable3 T3 on T2.ID2 = T3.info3ref

current result
<ID2...ID3>
9.....1
9....,2
10...null

Wanted Result:
9......2
10....null

(if col ID3 is not null,m then its value must larger then 1)

Thx

be a hardworking people!!

acdacd
Yak Posting Veteran

63 Posts

Posted - 2005-08-19 : 04:48:19

Also,
If i want count the no of row in TestTable3 that link with each TestTable2
ie
Result Set
<ID......Count of Row>
9..........2
10.........0

How to do so ?

be a hardworking people!!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-19 : 05:06:25

This will work

select T2.ID2, max(T3.ID3) from
TestTable2 T2 left join TestTable3 T3 on T2.ID2 = T3.info3ref
group by T2.ID2

For counting, use this

select T2.ID2, max(isnull(T3.ID3,0)) from
TestTable2 T2 left join TestTable3 T3 on T2.ID2 = T3.info3ref
group by T2.ID2

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources