Give values from sub select and re-enter back in query

My dbo.Report t开发者_运维技巧able has a column called Name. I need to somehow select the Name column in my sub select. How can I get the Name values from the sub select as well? Once I have those I need to be able to run another select query such as this:

SELECT * FROM MyOtherTable WHERE Name = @pName

where @pName being a newly created variable with values from the sub-select possibly?? I'm not sure how that works. Or something like this:

SELECT * FROM MyOtherTable WHERE Name IN (the values from my sub select go here)

PROC:

SELECT        ListingInfo, 
              COALESCE((SELECT SUM(ListingViews) 
              FROM dbo.Report
              WHERE (ID = @pID) 
              AND (DateEntered BETWEEN DATEADD(MONTH, 0, @pFromDate) AND DATEADD(MONTH, 1, @pToDate)-1)
              GROUP BY ID), 0) AS 'Views'
FROM dbo.Reporting r
INNER JOIN dbo.Listings l ON (r.ID = l.ID)
WHERE (r.ID = @pID)
AND l.TypeCode = 20

You can use a table variable to store your names.

DECLARE @Names TABLE
(
  Name varchar(250),
  SomeInt int
)

INSERT INTO @Names (Name, SomeInt)
SELECT Name, sum(ListingViews)
FROM WhateverTable
GROUP BY Name

SELECT * FROM OtherTable WHERE Name IN (SELECT Name FROM @Names)

You can go on to use @Names as if it were any other table, and if you use it in a stored procedure, it will automatically handle the clean up of the table variable when it ends.

Something like this? Or what else do you need? Didn't understand it at all, sorry.

SELECT * 
FROM MyOtherTable 
WHERE Name IN (SELECT name FROM Table WHERE Name = @pName)

Edit: Something like this?

 SELECT COUNT(*), name
 FROM MyOtherTable O,  Table T
 WHERE O.Name = T.NAME
 AND T.NAME = @pName
 group by name