Home » SQL & PL/SQL » SQL & PL/SQL » Need help with SQL (Oracle 12c)
| Need help with SQL [message #661763] |
Fri, 31 March 2017 03:31  |
 |
jvanh
Messages: 17
Registered: August 2013
|
Junior Member |
|
|
Your help is much appreciated:
I need to find ONLY those ID's for which there are ONLY records of type A (not any other) and minimally one record of type A with state 0. In below example the result should be 1 and 3.
ID | TYPE | STATE
---------------------------
1 | A | 0
1 | A | 1
1 | A | 1
1 | A | 0
2 | A | 1
2 | B | 0
2 | A | 0
3 | A | 1
3 | A | 0
3 | A | 1
3 | A | 1
4 | A | 1
4 | A | 1
4 | A | 1
5 | A | 0
5 | A | 0
5 | A | 0
5 | J | 0
5 | A | 0
|
|
|
|
| Re: Need help with SQL [message #661764 is a reply to message #661763] |
Fri, 31 March 2017 03:47  |
John Watson
Messages: 8929
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Please [code] tags for displaying that sort of thing, you have been asked before. Also you need to provide the CREATE TABLE and the INSERT statements (again, with [code] tags).
What SQL have tried so far? My first thought was a compound query: select the rows with A, then subtract the rows without A.
By the way, I wish you would not say "record" when you mean "row".
|
|
|
|
| Re: Need help with SQL [message #661765 is a reply to message #661764] |
Fri, 31 March 2017 04:07 |
|
jvanh
Messages: 17
Registered: August 2013
|
Junior Member |
|
|
1. Create table:
CREATE TABLE TEST
( "ID" NUMBER(1,0),
"TYPE" VARCHAR2(1 BYTE),
"STATE" NUMBER(1,0)
);
2. Insert data
Insert into TEST (ID,TYPE,STATE) values (1,'A',0);
Insert into TEST (ID,TYPE,STATE) values (1,'A',1);
Insert into TEST (ID,TYPE,STATE) values (1,'A',1);
Insert into TEST (ID,TYPE,STATE) values (1,'A',0);
Insert into TEST (ID,TYPE,STATE) values (2,'A',1);
Insert into TEST (ID,TYPE,STATE) values (2,'B',0);
Insert into TEST (ID,TYPE,STATE) values (2,'A',0);
Insert into TEST (ID,TYPE,STATE) values (3,'A',1);
Insert into TEST (ID,TYPE,STATE) values (3,'A',0);
Insert into TEST (ID,TYPE,STATE) values (3,'A',1);
Insert into TEST (ID,TYPE,STATE) values (3,'A',1);
Insert into TEST (ID,TYPE,STATE) values (4,'A',1);
Insert into TEST (ID,TYPE,STATE) values (4,'A',1);
Insert into TEST (ID,TYPE,STATE) values (4,'A',1);
Insert into TEST (ID,TYPE,STATE) values (5,'A',0);
Insert into TEST (ID,TYPE,STATE) values (5,'A',0);
Insert into TEST (ID,TYPE,STATE) values (5,'A',0);
Insert into TEST (ID,TYPE,STATE) values (5,'J',0);
Insert into TEST (ID,TYPE,STATE) values (5,'A',0);
commit;
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Need help with SQL [message #661804 is a reply to message #661769] |
Mon, 03 April 2017 05:46 |
John Watson
Messages: 8929
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
How about this?
1 Select * from
2 (select id from test where type='A'
3 minus
4 select id from test where type <>'A') withzero
5* where exists (select 1 from test zero where zero.id=withzero.id and zero.state=0)
jw122pdb> /
ID
----------
1
3
jw122pdb> |
|
|
|
| Re: Need help with SQL [message #661806 is a reply to message #661804] |
Mon, 03 April 2017 07:03 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
That's over-complicating it:
SQL> select id from test where type = 'A' and state = 0
2 minus
3 select id from test where type <> 'A';
ID
--
1
3
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Apr 19 04:46:54 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
