Not Having - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Not Having

Author

Topic

jdelgado
Starting Member

5 Posts

Posted - 2012-09-20 : 23:07:27

Hi,
I been trying to build a query to find out what records are missing a value and so far no luck. What I am trying to get is the RecipeId that don't have a PhaseId =20 in the table tblRecipeSeg. Each recipe can have multiple Segments with and each segment has a PhaseId but I am looking for the recipe that dont have PhaseId 20 like in the sample below recipe Id 39 doesnt have a phaseId of 20.

Thanks for your help

tables are as follow.
tblRecipe
RecipeId
Revision
Description
Comments
....

tblPhase
PhaseId
Description
....

tblRecipeSeg
SegKey
RecipeId
SegId
PhaseId

Sample Data
SegKey RecipeId SegId PhaseId
27 4 1 17
28 4 2 18
29 4 3 19
31 4 4 20
32 4 5 21
33 4 6 22
34 4 7 23
326 39 1 17
327 39 2 18
328 39 3 19
330 39 4 21
331 39 5 22
332 39 6 23
333 40 1 17
334 40 2 18
335 40 3 19
336 40 4 20
337 40 5 21
338 40 6 22
339 40 7 23

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-20 : 23:11:52

[code]
SELECT r.*
FROM tblrecipe r
INNER JOIN (SELECT RecipeId
FROM tblRecipeSeg
GROUP BY RecipeId
HAVING SUM(CASE WHEN PhaseId=20 THEN 1 ELSE 0 END) =0
)s
ON s.RecipeId = r.RecipeId
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jdelgado
Starting Member

5 Posts

Posted - 2012-09-21 : 07:51:24

WOW, Thanks a lot

quote:
Originally posted by visakh16

SELECT r.*
FROM tblrecipe r
INNER JOIN (SELECT RecipeId
FROM tblRecipeSeg
GROUP BY RecipeId
HAVING SUM(CASE WHEN PhaseId=20 THEN 1 ELSE 0 END) =0
)s
ON s.RecipeId = r.RecipeId

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-21 : 12:24:49

Welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources