Return rows with a column that tells how many times id is in another table

I have 2 tables. I want to return everything from Table1 where ItemId=2. I want to add one column CountUnits that tells how many times UnitId exists in Table2. How do I write a stored procedure for this?

Table 1
T1Id, ItemId, UnitId, T1Name
Table 2
T2Id, UnitId, T2Name.

Parameter: ItemId
Return:   T1Id, ItemId, UnitId,T1Name, CountUnits
johnkainnAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

tigin44Commented:
this query will give you te result

select t1.T1Id, t1.ItemId, t1.UnitId, t1.T1Name, count(*) as CountUnits
from table1 t1
         inner join table2 t2 on t1.UnitId = t2.UnitId
where t1.ItemId = 2
group by t1.T1Id, t1.ItemId, t1.UnitId, t1.T1Name
0
tigin44Commented:
and as a spit can be like this

CREATE PROCEDURE spCountUnits
    @itemId               int,
    @countUnits        int OUTPUT
AS

select t1.T1Id, t1.ItemId, t1.UnitId, t1.T1Name, @countUnits = count(*)
from table1 t1
         inner join table2 t2 on t1.UnitId = t2.UnitId
where t1.ItemId = @itemId
group by t1.T1Id, t1.ItemId, t1.UnitId, t1.T1Name;



and you can call it like

declare @countUnit    int;
exec  spCountUnits  2(will be your itemId), @countUnit OUTPUT;
select @countUnit;


0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rizwanidreesCommented:
CREATE PROCEDURE spCountUnits
    @itemId               int
AS

select t1.T1Id, t1.ItemId, t1.UnitId, t1.T1Name, count(t2.UnitId) countUnits
from table1 t1
         inner join table2 t2 on t1.UnitId = t2.UnitId
where t1.ItemId = @itemId
group by t1.T1Id, t1.ItemId, t1.UnitId, t1.T1Name;
0
johnkainnAuthor Commented:
Thanks. If I use this I only get returned the rows with matching unitid in table 2.
I would like to get all rows returned from table 1. If there is no mathching unitId in table 2 then I would like to have "0" in CountUnits column, but otherwise the number of times particular unitId appears in table 2.
For example if unitId=2 is in 3 rows in table 2 CountUnits=3. If there is no row with unitid=3 in Table 2 then CountUnits=0.


0
rizwanidreesCommented:
CREATE PROCEDURE spCountUnits
    @itemId               int
AS

select t1.T1Id, t1.ItemId, t1.UnitId, t1.T1Name, count(t2.UnitId) countUnits
from table1 t1
         left join table2 t2 on t1.UnitId = t2.UnitId
where t1.ItemId = @itemId
group by t1.T1Id, t1.ItemId, t1.UnitId, t1.T1Name;
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.