October 22, 2015 at 1:08 pm
Hi there, I know this is probably a basic SQL question,
Here's what my table looks like;
UniqID | Code |
1 | ABC
1 | 123
2 | ABC
3 | ABC
4 | ABC
4 | ABC
I only want to UniqIds that only have the CODE of ABC... and if it contains ANYTHING other than ABC then It doesnt return that UniqID... Now keep in mind there's multiple different codes.. I'm just looking for a bit of code that drops any ID's that don't have my criteria, Any help is appreciated.. thanks!
October 22, 2015 at 1:47 pm
Would something like this work?
SELECT DISTINCT UniqID
FROM <table>
WHERE Code = 'ABC'
or
SELECT UniqID, CODE
FROM <table>
WHERE Code = 'ABC'
Edit: I've misunderstood the query, and have updated my answer to something more appropriate.
October 22, 2015 at 1:49 pm
So many ways to do this, that I can't decide the best option.:-D
Here are 2 ways.
CREATE TABLE #SampleData (
UniqID int,
Code varchar(10)
);
INSERT INTO #SampleData
VALUES
(1, 'ABC'),
(1, '123'),
(2, 'ABC'),
(3, 'ABC'),
(4, 'ABC'),
(4, 'ABC');
SELECT *
FROM #SampleData
WHERE UniqID NOT IN (SELECT UniqID
FROM #SampleData
WHERE Code <> 'ABC');
WITH CTE AS(
SELECT *,
MAX( NULLIF( Code, 'ABC')) OVER(PARTITION BY UniqID) InvalidCodes
FROM #SampleData
)
SELECT UniqID, Code
FROM CTE
WHERE InvalidCodes IS NULL;
GO
DROP TABLE #SampleData
October 22, 2015 at 2:22 pm
Edit: removed, I think I've misunderstood the question.
October 22, 2015 at 2:24 pm
SELECT UniqID
FROM #SampleData
GROUP BY UniqID
HAVING MIN(Code) = 'ABC' AND MAX(Code) = 'ABC'
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 22, 2015 at 3:21 pm
Thank you Everyone!
Luis! I was trying to add more codes to this section would the syntax look something like this?
SELECT *
FROM #SampleData
WHERE UniqID NOT IN (SELECT UniqID
FROM #SampleData
WHERE Code <> 'ABC', 'DEF', 'HIJ');
Thanks!
October 22, 2015 at 3:36 pm
Scott did my first solution (that one only scans the table once)...
Here's another way:
SELECT UniqID
FROM #SampleData
WHERE Code = 'ABC'
EXCEPT
SELECT UniqID
FROM #SampleData
WHERE Code <> 'ABC'
-- Itzik Ben-Gan 2001
October 23, 2015 at 1:31 am
Alan.B (10/22/2015)
Scott did my first solution (that one only scans the table once)...Here's another way:
SELECT UniqIDFROM #SampleData
WHERE Code = 'ABC'
EXCEPT
SELECT UniqID
FROM #SampleData
WHERE Code <> 'ABC'
And another:
SELECT o.UniqID
FROM #SampleData o
WHERE o.Code = 'ABC'
AND NOT EXISTS (
SELECT 1
FROM #SampleData i
WHERE i.UniqID = o.UniqID
AND i.Code <> 'ABC'
)
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 23, 2015 at 1:48 am
Hmm, I am slightly confused here because as per my understanding this wiil be a simple or rather a basic query to fetch those code having only ABC.
Is there something that I am missing or is it that I have not understood the question.
October 23, 2015 at 1:52 am
ranjitdaljitmand (10/23/2015)
Hmm, I am slightly confused here because as per my understanding this wiil be a simple or rather a basic query to fetch those code having only ABC.Is there something that I am missing or is it that I have not understood the question.
Your understanding is the same as those who have already provided a solution. Are you offering an alternative solution to those already posted? There are a few remaining.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 23, 2015 at 7:44 am
ranjitdaljitmand (10/23/2015)
Hmm, I am slightly confused here because as per my understanding this wiil be a simple or rather a basic query to fetch those code having only ABC.Is there something that I am missing or is it that I have not understood the question.
You might be thinking on doing something like:
SELECT *
FROM #SampleData
WHERE Code = 'ABC'
That won't work because it will return rows that have code 'ABC', but also have other rows with same UniqID but different code. That's why it gets a little bit more complicated.
October 23, 2015 at 7:49 am
rourrourlogan (10/22/2015)
Thank you Everyone!Luis! I was trying to add more codes to this section would the syntax look something like this?
SELECT *
FROM #SampleData
WHERE UniqID NOT IN (SELECT UniqID
FROM #SampleData
WHERE Code <> 'ABC', 'DEF', 'HIJ');
Thanks!
As you should know, a comparison operator can only have one value in each side of it. For that, you need to use something else. Check this which will show you more detail of what you need: https://msdn.microsoft.com/en-us/library/ms177682.aspx
By the way, we're volunteers here and if we don't answer in the forum is probably because we have other things to do. Please avoid sending private messages to ask questions that should go in the forum.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply