Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2003
    Posts
    176

    Unanswered: 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.

    V

  2. #2
    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. #3
    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
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    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. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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 )

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I agree with r937. You should not need to take an actual count to do this.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •