Myujth

I'm sure this is easy but I'm still getting syntax errors when I run it in SQL Server Management Studio v17.4.

I have table T1 which has serial numbers, and I just need to iterate through and check how many times they appear in T2. I tried like

declare @serial int

select @serial = select serial from T1

while @serial
begin
 select count(*) from T2 where Serial = @serial
end

But I get an error:

Incorrect syntax near the keyword 'select'

How to do it? Thanks.

First of all, doing:

select @serial = select serial from T1

while @serial

…

Doesn't mean that it will start to magically loop for every value of serial that T1 has, it will just assign one value of serial to your variable (if it was correctly written anyway select @serial = serial from T1).

What you want doesn't really make sense to do it in a loop; sql works in sets, and you should try to write your code accordingly. In this case, a simple JOIN should do:

SELECT T1.Serial,
 ISNULL(COUNT(T2.Serial),0) N
FROM T1
LEFT JOIN T2
 ON T1.Serial = T2.Serial
GROUP BY T1.Serial
;

Instead of while loop, just join the tables and use an aggregate.
Something like:

select a.serial, count(a.serial) from t2 a
inner join t1 b on b.serial = a.serial
group by a.serial

Without sample data I can't test it out for you, but that will perform a lot better for you.

Simply

 SELECT T1.Serial,
 COUNT(T2.Serial) AppearsInT2
 FROM T1 JOIN T2 ON T1.Serial = T2.Serial
 GROUP BY T1.Serial

There is no need to declare a variable or to use a loop.

If you want to return 0 for serials which not exists in the second table use LEFT JOIN instead

SELECT T1.Serial,
 COUNT(T2.Serial) AppearsInT2
FROM T1 LEFT JOIN T2 ON T1.Serial = T2.Serial
GROUP BY T1.Serial;

Simple Demo

Yes it is a syntax error

select @serial = select serial from T1; -- is wrong it should as written below

select @serial = serial from T1 ;

This will select the 1st value from Table T1.

This will remove the error but the query written will not yeald the required output.

You need to loop through table T1 and for each value of T1 search in T2.

See if the below simple query helps

select serial, count(Serial) as SerilaCount from T1 inner join T2 on T1.serial =T2.Serial
group by T1.serial.

Seems like you could just do this in one quick statement, rather than a loop.

SELECT T2.Serial, Count(T2.Serial) as NumOfSerial
FROM T1 
 INNER JOIN T2 ON T1.Serial = T2.Serial
GROUP BY T2.Serial