CODEWITHSUNDEEP
sql-server
Pr0no
Pr0no

Reputation: 4099

How to compare a count() to a value from another table?

I have 2 tables. I want to compare the number of rows in table a to a value in table b:

select count(1)  from table_a; -- returns 1500
select row_count from table_b; -- returns 1490

I want to compare the numbers and perform and write a line into table c accordingly. I thought about doing a join but this doesnt make sense since the values from a and b are unrelated:

select
      count(a.*) as rows_in_table
    , b.lines_in_loadfile
from
    [table_a] a
    cross join [table_b]    
;

Now I am confused. In pseudo what I need is:

select
    count(1) from table_a as value_a
    row_count from table_b as value_b

case
    when value_a <> value_b then msg = 'values do not match'
    else msg = 'values match'
end

insert into table_c (row_msg) = @msg;

How can I accomplish this in 1 query? I'm quite sure it's not that hard, but have been staring at it for an hour now and am stuck.

Upvotes: 0

Views: 4292

Answers (3)

Galma88
Galma88

Reputation: 2546

This is the way: 

INSERT INTO TABLE_C
    (row_msg)

    SELECT
          CASE
        WHEN COUNT(a.*) <> b.lines_in_loadfile THEN 'Values do not match'
        ELSE 'Values match'
    END
    FROM
        [table_a] a
        cross join [table_b]

Upvotes: 0

A ツ
A ツ

Reputation: 1267

assuming you have only one row in each table: 

SELECT a.value_a, b.value_b
     , msg  = case
       when value_a = b.value_b then 'values do match'
       else 'values do not match'
       end
FROM
( select count(*)  value_a from table_a) a ,
( select row_count value_b from table_b) b

Upvotes: 1

Mithrandir
Mithrandir

Reputation: 25337

insert into table_c (row_msg)
select
    CASE
       WHEN 
          (selectcount(1) from table_a ) != 
          (select row_count from table_b) THEN 'values do not match'
       ELSE 'values match' 
    END

I'm not sure about the "select row_count from table_b" part. How do you make sure this returns only a single value?

Upvotes: 1

Related Questions