Stored Procedure FROM clause

[Mike555]

I have a stored procedure which looks at both a master table and a detail table. Part of the code is below.

This should display one record for each master record where data tblDetail.UsedBy = 'mike'. However, when I run this I get a record for each detail record.

For example, If record "123" in tblMaster has 5 detail records in tblDetail where tblDetail.UsedBy = 'mike' then I get 5 returned records after the stored proc runs. I only want to get 1 returned record in this example.

How can I modify this so that I can use the detail table criteria in my WHERE clause but limit the returned values to only those from the master table?

SELECT  dbo.tblMaster.FlitchNum

FROM	dbo.tblMaster INNER JOIN
	    dbo.tblDetail ON dbo.tblMaster.FlitchNum =  dbo.tblDetail.FlitchNum

WHERE tblDetail.UsedBy = 'mike'
ORDER BY tblDetail.DateUsed
GO

Thanks.

--
Mike

 

[gradley]

Try this:

SELECT FlitchNum
FROM    dbo.tblMaster 
Where FlitchNum IN (Select Distinct FlitchNum from dbo.tbldetail where UsedBy = 'mike' ) 
GO

 

[dky1e]

Try this:

SELECT  dbo.tblMaster.FlitchNum
FROM   dbo.tblMaster   
WHERE exists( select 1 from  dbo.tblDetail  
              where dbo.tblDetail.FlitchNum = dbo.tblMaster.FlitchNum 
              and tblDetail.UsedBy = 'mike' )

 

[dky1e]

lol

 

[gradley]

The race is on...

 

[Mike555]

Easily the quickest replies I've ever received. Thank you both, the code from both of you worked perfectly!

--
Mike

 

[ESquared]

The correlated subquery might be a little less efficient, though (the second method).

I personally prefer joins to using the IN clause:

SELECT FlitchNum
FROM
   dbo.tblMaster M
   INNER JOIN (
      SELECT DISTINCT FlitchNum
      FROM dbo.tblDetail
      WHERE UsedBy = 'mike') F
   ) ON F.FlitchNum = M.FlitchNum

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)

 

[ESquared]

My query has a mistake. Remove the second closing parenthesis:

SELECT FlitchNum
FROM
   dbo.tblMaster M
   INNER JOIN (
      SELECT DISTINCT FlitchNum
      FROM dbo.tblDetail
      WHERE UsedBy = 'mike'
   ) F ON F.FlitchNum = M.FlitchNum

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)