1. Registered User
Join Date
Sep 2003
Posts
176

Hi all,

I have a need to compare the number of rows returned from table A when it is joined to table B to the number of rows returned when there are no joins involved. If the number of rows returned are the same, then I need to proceed to execute my next step else end.

So, If RowCount A = RowCount A when A joined to B
THEN Goto Next Step
Else End

I need to put the above logic in a sp that I want to execute using a job.

Help is appreciated.

V

2. Registered User
Join Date
Oct 2003
Posts
268
Code:
```DECLARE @countTotal INTEGER
SELECT @countTotal = COUNT(*) FROM TableA

DECLARE @countJoin INTEGER
SELECT @countJoin = COUNT(DISTINCT TableA.ID) FROM TableA INNER JOIN TableB ON (TableA.Col = TableB.Col)

IF @countTotal = @countJoin BEGIN
-- Do something
ELSE
-- Do something else
END```

3. Window Washer
Join Date
Nov 2002
Location
Jersey
Posts
10,322
Code:
```IF ( SELECT SUM(myCount)
FROM (
SELECT COUNT(*) AS myCount
FROM TableA
UNION ALL
SELECT COUNT(*)*-1 AS myCount
FROM TableA
INNER JOIN TableB
ON a.key = b.key)) AS XXX) = 0
BEGIN
-- Do something
END```

4. Registered User
Join Date
Oct 2006
Posts
5

## The better solution ;o))

declare @A table(Col int)
declare @B table(Col int)

insert @A values(1)
insert @A values(2)
insert @B values(1)

declare @CountA int
,@CountJoinB int

select @CountA = count(A.Col)
,@CountJoinB = count(B.Col)
from @A A left join @B B on A.Col = B.Col

select @CountA,@CountJoinB

5. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002

notice that you don't actually have to take any counts in order to determine whether to proceed

select 'stop' from A
where not exists (
select 937 from B
where B.FK = A.PK )

6. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595