[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 312
  • Last Modified:

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!!!
0
ravichand-sql
Asked:
ravichand-sql
1 Solution
 
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
 
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
 
John_VidmarCommented:
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

Featured Post

Get expert help—faster!

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

Tackle projects and never again get stuck behind a technical roadblock.
Join Now