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