How to select records from a Table that has a certain number of rows in a related table in SQL Server?

Not quite sure how to ask this, but I have 2 tables that are related in a 1 to many relationship, I need to select all records in the "1" table that have less than three records in the "many' table.

select b.foreignkey,count(b.foreignkey) as bidcount
from b 
where b.foreignkey in (select a.id from a) and bidcount< 3 
group by b.foreignkey

this 开发者_开发问答doesn't work at all I know but I am at a loss how to do this.

I need to in the end select all the records from the "a" table based on this criteria. Sorry if that is confusing!

Just using your code, not tested:

SELECT
   b.foreignkey,
   count(b.foreignkey) as bidcount 
FROM 
   b
WHERE 
   b.foreignkey IN (SELECT a.id FROM a) 
GROUP BY 
   b.foreignkey 
HAVING 
   count(b.foreignkey) < 3  

Try this:

SELECT t1.id,COUNT(t2.parentId)
FROM table1 as t1
INNER JOIN table2 as t2
ON t1.id = t2.parentId
GROUP BY t1.id
HAVING COUNT(t2.parentId) < 3

You didn't mention which version of SQL Server you're using - if you're on SQL Server 2005 or newer, you could use this CTE (Common Table Expression):

;WITH ChildRows AS
(
   SELECT A.Id, COUNT(b.Id) AS 'BCount'
   FROM
      dbo.TableA A
   INNER JOIN
      dbo.TableB B ON B.TableAId = A.Id
)     
SELECT A.*, R.BCount
FROM dbo.TableA A
INNER JOIN ChildRows R ON A.Id = R.Id

The inner SELECT lists the Id columns from TableA and the count of the child rows associated with those (using the INNER JOIN to TableB) - and the outer SELECT just builds on top of that result set and shows all fields from table A (and the count from the B table)

if you want to return all fields of your (1) table in one query, I suggest you consider using CROSS APPLY:

SELECT t1.* FROM table_1 t1
CROSS APPLY (SELECT COUNT(*) cnt FROM Table_Many t2 WHERE t2.fk = t1.pk) a
where a.cnt < 3

in some particular cases, based on your indices and db structure, this query may run 4 times faster than the GROUP BY method

you have posted this question in sql server, I have a answer in oracle database system (don't know whether it will run in sql server as well or not)

this is as follow-

select [desired column list] from 
    (select b.*, count(*) over (partition by b.foreignkey) c_1
      from b
     where b.foreignkey in (select a.id from a) )

where c_1 < 3 ;

i hope it should work on sql server as well... if not please let me update ..