troubleshooting Question

Return IDs with a single value in another field

Avatar of ttist25
ttist25 asked on
Microsoft SQL Server
2 Comments1 Solution75 ViewsLast Modified:
Working in SQL Server 2012.

I'm trying to find rows in my table where an ID exists with only 1 distinct value in another field:

My table looks something like this:
CREATE TABLE #tmpMyTable (
      MyID int NULL,
      MyField char NULL
      VALUES      (1, 'A'),
                  (1, 'B'),
                  (1, 'B'),
                  (2, 'A'),
                  (3, 'A'),
                  (3, 'B'),
                  (3, 'B'),
                  (3, 'C'),
                  (4, 'A'),
                  (4, 'A'),
                  (5, 'D');

The query I'm trying to build would return the following:

2, A
4, A
5, D

I feel like this should be fairly simple but I'm failing miserably at it.  I've tried a SELF JOIN, GROUP BY, OVER PARTITION BY, etc. and I can't seem to get it.  

Any help will be greatly appreciated!

Join our community to see this answer!
Unlock 1 Answer and 2 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros