asked
count based on two @table variables
Hi, I have a table var @a which stores all IDs. I have another table var @b which stores all table names. I need to count rows of each table (i.e. one row of @b table var) based on one ID (i.e. one row of @a table var) something like select count(*) from @b where id = @
a.id what is the best way to do this? ...if I try above query, it says "must declare @b variable" cursors would cause too much complexity, I think.. I need to loop through each table for each ID and count rows in each of the table for each ID.. any ideas??...
A simple join will do this for you declare @a table (id int); declare @b table (id int); insert into @a select 1; insert into @a select 2; insert into @a select 3; insert into @a select 4; insert into @b select 1; insert into @b select 1; insert into @b select 1; insert into @b select 2; insert into @b select 3; insert into @b select 3; insert into @b select 3; select
a.id, count(
b.id) from @a a left join @b b on
a.id =
b.id group by
a.id; gives id ----------- ----------- 1 3 2 1 3 3 4 0 (4 row(s) affected)
table 1 create table table1 ( ID varchar(11) null, name varchar(40) ) create table table220130501 ( trx_name varchar(20), trxdate datetime, trx_ID varchar(11) ) Above table is only for 1 day. There will be one such table for each day and I want to run my query on all tables of a month (say 30) table names will be like table220130501, table220130502 etc. the trxID in table220130501 will same as ID in table 1. sample data-table1 ID name 1 abc 2 xyz 3 pqr sample data-table2 (this is data in one table say table220130501). trx_name trx_date trx_ID pen 2013-05-01 1 paper 2013-05-01 1 stapler 2013-05-01 3 bottle 2013-05-01 2 nib 2013-05-01 2 so I want to count for each trx_ID (i.e.1,2, 3) in table220130501, how many rows exist in that table. the source for the trx_ID is table1.However, I need to count this for each such daily table so for 1 month say MAY, there will be 31 tables from table220130501 to table220130531. I know I can do this manually by going to each table, but I do not want to do that. hence, I was trying to put the trx_IDs and each day tablenames in different table variables and trying. I hope you guys
understand.is there any better way to do this? thanks
12 People are following this question.
