Sunday, March 11, 2012

Compare Row Counts

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.

VDECLARE @.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|||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|||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|||LEFT OUTER JOIN is definitely the right way to think about this problem

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 )

;)|||I agree with r937. You should not need to take an actual count to do this.

No comments:

Post a Comment