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 Go to next message
jvanh
Messages: 12
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 Go to previous messageGo to next message
John Watson
Messages: 7266
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 Go to previous messageGo to next message
jvanh
Messages: 12
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 #661766 is a reply to message #661765] Fri, 31 March 2017 04:09 Go to previous messageGo to next message
John Watson
Messages: 7266
Registered: January 2010
Location: Global Village
Senior Member
That's good! And what SQL have you tried so far?
Re: Need help with SQL [message #661767 is a reply to message #661766] Fri, 31 March 2017 05:41 Go to previous messageGo to next message
John Watson
Messages: 7266
Registered: January 2010
Location: Global Village
Senior Member
I've jusdt written a solution - it returns 1, 3, and 4. Do you not want 4?
Re: Need help with SQL [message #661768 is a reply to message #661767] Fri, 31 March 2017 06:06 Go to previous messageGo to next message
jvanh
Messages: 12
Registered: August 2013
Junior Member
Indeed, it should not return 4 as there's no row for ID='4' with STATE='0'.

I got sidetracked hence no feedback from my side yet.
Re: Need help with SQL [message #661769 is a reply to message #661768] Fri, 31 March 2017 07:01 Go to previous messageGo to next message
jvanh
Messages: 12
Registered: August 2013
Junior Member
Ok, think I got it now:

select ID FROM
(
 select 
  ID, TYPE 
 from TEST
 WHERE STATE=0
 GROUP BY ID, TYPE
) temptable
GROUP BY ID
HAVING COUNT(*) = 1 and min(TYPE)='A';

[Updated on: Fri, 31 March 2017 07:41]

Report message to a moderator

Re: Need help with SQL [message #661772 is a reply to message #661769] Fri, 31 March 2017 13:55 Go to previous messageGo to next message
Michel Cadot
Messages: 65389
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Are you sure this satisfy "for which there are ONLY records of type A (not any other)"?

Re: Need help with SQL [message #661803 is a reply to message #661772] Mon, 03 April 2017 03:45 Go to previous messageGo to next message
cookiemonster
Messages: 13017
Registered: September 2008
Location: Rainy Manchester
Senior Member
Which is to say - what happens if the other types don't have state=0?
Re: Need help with SQL [message #661804 is a reply to message #661769] Mon, 03 April 2017 05:46 Go to previous messageGo to next message
John Watson
Messages: 7266
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 Go to previous messageGo to next message
cookiemonster
Messages: 13017
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

Re: Need help with SQL [message #661807 is a reply to message #661806] Mon, 03 April 2017 07:05 Go to previous message
John Watson
Messages: 7266
Registered: January 2010
Location: Global Village
Senior Member
Embarassed
Previous Topic: Converting comma separated value in different rows
Next Topic: How can I convert a SQL command to MySql
Goto Forum:
  


Current Time: Sun Feb 25 17:47:32 CST 2018

Total time taken to generate the page: 0.01854 seconds