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 TABLE1SELECT DISTINCT ACCT_ID FROM TABLE1 WHERE ACCT_TYPE IN ('A','B') AND ACCT_TYPE NOT IN ('C','D','E')
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!!!
Microsoft SQL Server 2008Microsoft SQL Server 2005
Last Comment
John_Vidmar
8/22/2022 - Mon
Pratima
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.
rc
ASKER
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
Pratima
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'
>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.
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.