Solved

Return records not matching criteria using group by

Posted on 2008-10-06
1
152 Views
Last Modified: 2012-05-05
I have a table that contains text type information for a given case.  There can be 1 or more text type for a given case.  I am trying to write a query that will return only those case numbers that do not have a text_type of resolution for any of the text types for a given case

 Here is some sample  data

Case_id     Text_Type_Seq   Text_Type        
1                1                           Notes
1                2                           Notes
1                3                           Resolution
2                1                           Notes
2                2                           Notes
2                3                           Notes
2                4                           Notes
3                1                           Notes
3                2                           Notes
4                1                           Notes
4                2                           Resolution


Using the sample data above:

The query should only return

Case_ID
2
3

Since case 1 and 4 have a text type of resolution
0
Comment
Question by:johnnyg123
1 Comment
 
LVL 32

Accepted Solution

by:
Daniel Wilson earned 500 total points
ID: 22650132
Select distinct Case_ID
From MyTable T1
Where Not Exists (Select Case_ID From MyTable T2 Where T1.Case_ID = T2.Case_ID And T2.Text_type = 'Resolution')
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now