0

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Daniel Simpkins
  • 674
  • 5
  • 18

2 Answers2

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
FumblesWithCode
  • 441
  • 3
  • 7
0

Sure it's not SQL. You're missing the right operand of the second LEFT JOIN:

SELECT *
FROM Table1 LEFT JOIN Table2
    ON Table1.ID = Table2.ID
    LEFT JOIN ????? WHUT ?????
WHERE Table1.Key = @key
AND (SELECT COUNT(*) FROM Table2 WHERE ID = Table1.ID AND OtherID = @otherID) < Table1.NumberInUse;
pid
  • 11,472
  • 6
  • 34
  • 63
  • I have corrected the original query. Sorry I chopped up a bigger query to take out the relevant parts and accidentally left that join in there. – Daniel Simpkins Apr 17 '14 at 17:31