Using COUNT For Comparison in SQL Server CE 4.0

Question

I'm attempting to combine the logic for some of my SQL queries, and I can't seem to figure out this problem. Obviously SQL Server CE has many limitations compared to SQL Server or mySQL, but surely there's a way to solve this.

I want to do a count on one table in my database, based on some parameters, and then I want to compare this value to a value stored in a column in another table.

Let's say the database is modeled like this:

Table1:

ID int
Key string
NumberInUse int

Table2:

ID int
OtherID int

Here's the necessary parts of the query.

SELECT * 
FROM Table1 
LEFT JOIN Table2 ON Table1.ID = Table2.ID
WHERE Table1.Key = @key
AND (SELECT COUNT(*) FROM Table2 WHERE ID = Table1.ID AND OtherID = @otherID) < Table1.NumberInUse;

Unfortunately this query gives me this error:

There was an error parsing the query. [ Token line number = 4,Token line offset = 6,Token in error = SELECT ]`

So is there a way I can rephrase the WHERE clause of my query to utilize this comparison?

Answer 1

Try this:

SELECT * 
FROM Table1 t1
INNER JOIN (SELECT ID
                  ,COUNT(*) numCount
              FROM Table2 t2
             WHERE t2.OtherId = @otherID
             GROUP BY ID) t3
   ON t1.ID = t3.ID
 WHERE t1.Key = @Key
   AND t3.numCount < t1.NumberInUse