t-sql query help

Hi,

I know this is a small issue but I am trying to figure this out for a while now. Please help me resolve the below issue.

CREATE TABLE TABLE1
(
ACCT_ID INT,
ACCT_TYPE VARCHAR(20)
)

INSERT INTO TABLE1 (ACCT_ID,ACCT_TYPE) VALUES (1,'A')
INSERT INTO TABLE1 (ACCT_ID,ACCT_TYPE) VALUES (1,'B')
INSERT INTO TABLE1 (ACCT_ID,ACCT_TYPE) VALUES (1,'C')
INSERT INTO TABLE1 (ACCT_ID,ACCT_TYPE) VALUES (1,'D')
INSERT INTO TABLE1 (ACCT_ID,ACCT_TYPE) VALUES (1,'E')

INSERT INTO TABLE1 (ACCT_ID,ACCT_TYPE) VALUES (2,'A')
INSERT INTO TABLE1 (ACCT_ID,ACCT_TYPE) VALUES (2,'B')

SELECT * FROM TABLE1

SELECT DISTINCT ACCT_ID FROM TABLE1 WHERE ACCT_TYPE IN ('A','B') AND ACCT_TYPE NOT IN ('C','D','E')

Open in new window


From the above TABLE1, I want only the ACCT_ID's which have 'A' , 'B'. I want to ignore the other IDs.

From the above table I should get the result as '2'. But I am getting both '1' and '2'

Please help me modifying my query,

Thanks in advance!!!
ravichand-sqlAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
Advertise Here
 
John_VidmarConnect With a Mentor Commented:
Solution 1, using a correlated sub-query: 
SELECT DISTINCT
	a.ACCT_ID
FROM	TABLE1	a
WHERE	a.ACCT_TYPE IN ('A','B')
AND	NOT EXISTS
	(	SELECT	*
		FROM	TABLE1
		WHERE	ACCT_ID = a.ACCT_ID
		AND	ACCT_TYPE IN ('C','D','E')
	)

Open in new window

Solution 2, preferred solution: 
SELECT DISTINCT
	a.ACCT_ID
FROM	TABLE1	a
LEFT
JOIN	TABLE1	b	ON	a.ACCT_ID = b.ACCT_ID
			AND	b.ACCT_TYPE IN ('C','D','E')
WHERE	a.ACCT_TYPE IN ('A','B')
AND	b.ACCT_ID IS NULL

Open in new window

Solution 3, in-clause: 
SELECT DISTINCT
	ACCT_ID
FROM	TABLE1
WHERE	ACCT_TYPE IN ('A','B')
AND	ACCT_ID NOT IN
	(	SELECT	ACCT_ID
		FROM	TABLE1
		WHERE	ACCT_TYPE IN ('C','D','E')
	)

Open in new window

0
 
Pratima PharandeCommented:
query is correct

SELECT DISTINCT ACCT_ID FROM TABLE1 WHERE ACCT_TYPE IN ('A','B') AND ACCT_TY

you want want only the ACCT_ID's which have 'A' , 'B'. right.

then see below
INSERT INTO TABLE1 (ACCT_ID,ACCT_TYPE) VALUES (1,'A')
INSERT INTO TABLE1 (ACCT_ID,ACCT_TYPE) VALUES (1,'B')
INSERT INTO TABLE1 (ACCT_ID,ACCT_TYPE) VALUES (2,'A')
INSERT INTO TABLE1 (ACCT_ID,ACCT_TYPE) VALUES (2,'B')

as per above you are inserting 1 and 2 from A and B
so result is correct.
0
 
ravichand-sqlAuthor Commented:
Thanks for the reply :)

Yes, But I dont want to get 1 in my result set as it has values other than 'A' and 'B'. My result set should only have 2
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Go Premium
 
Pratima PharandeCommented:
ok means you do not want the acc_id that has other valuse other than A and B is it so ?

then try this
Select ACCT_ID FROM TABLE1
where ACCT_ID not in (

SELECT ACCT_ID  FROM TABLE1 WHERE ACCT_TYPE IN ('C','D' , 'E') )
and  ACCT_TYPE = 'A' and  ACCT_TYPE = 'B'
0
 
ravichand-sqlAuthor Commented:
Hello Pratima,

The above query of yours is giving me no results. Please help me modify it.

Thanks in advance!
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>I want only the ACCT_ID's which have 'A' , 'B'
I take it this really means 'I want only the ACCT_ID's where the ACCT_TYPE value is 'A' or 'B'? 
SELECT DISTINCT ACCT_ID
FROM TABLE1
WHERE ACCT_TYPE IN ('A', 'B')
ORDER BY ACCT_ID

Open in new window

>From the above table I should get the result as '2'. But I am getting both '1' and '2'
Looking at your sample data there are ACCT_TYPE=A or B for both 1 and 2, so eyeball your sample data and explain to us the logic for returning only 2.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.