Home » SQL & PL/SQL » SQL & PL/SQL » Finding entries missing a value and listing all used values for specific data
Finding entries missing a value and listing all used values for specific data [message #681995] Wed, 23 September 2020 09:29 Go to next message
magriii
Messages: 3
Registered: September 2020
Junior Member
Sorry for the beginner questions. I've searched the forum, but I couldn't figure out who to build a search for my questions. Way too many results.

I have 2 questions. Data looks like this.

Name Category
A Cat1
A Cat2
A Cat3
B Cat1
B Cat2
C Cat1
C Cat3
D Cat2
D Cat3

Question 1: Selecting all names with Cat1 missing Cat2 and vice versa. Expected results:
C
D

Question 2: Listing all results from question 1 plus the used categories. Expected results:
C Cat1, Cat3
D Cat2, Cat3

Thanks for any help
Re: Finding entries missing a value and listing all used values for specific data [message #681996 is a reply to message #681995] Wed, 23 September 2020 10:05 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.

Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

There are many ways to solve these questions.
For Q1, you can use WHERE category = 'Cat1' AND NOT EXISTS (...)
For Q2, you can use the result of Q1: SELECT * FROM table WHERE name IN (Q1), you can aggregate each name in one line using LISTAGG function.
Or you can directly use this function in an inline view to aggregate each name and then select only the names that satisfy your requirements.

[Updated on: Wed, 23 September 2020 10:07]

Report message to a moderator

Re: Finding entries missing a value and listing all used values for specific data [message #681997 is a reply to message #681996] Wed, 23 September 2020 15:21 Go to previous messageGo to next message
magriii
Messages: 3
Registered: September 2020
Junior Member
Hi Michel,

thanks for the quick reply and your hints on code inserts. I try to do better.
Still can't get it to work.

CREATE TABLE "SAMPLE" 
(	"NAME" VARCHAR2(20 BYTE), 
    "CATEGORY" VARCHAR2(20 BYTE)
);

INSERT INTO "SAMPLE"  (NAME, CATEGORY) VALUES ('A', 'Cat1');
INSERT INTO "SAMPLE"  (NAME, CATEGORY) VALUES ('A', 'Cat2');
INSERT INTO "SAMPLE"  (NAME, CATEGORY) VALUES ('A', 'Cat3');
INSERT INTO "SAMPLE"  (NAME, CATEGORY) VALUES ('B', 'Cat1');
INSERT INTO "SAMPLE"  (NAME, CATEGORY) VALUES ('B', 'Cat2');
INSERT INTO "SAMPLE"  (NAME, CATEGORY) VALUES ('C', 'Cat1');
INSERT INTO "SAMPLE"  (NAME, CATEGORY) VALUES ('C', 'Cat3');
INSERT INTO "SAMPLE"  (NAME, CATEGORY) VALUES ('D', 'Cat2');
INSERT INTO "SAMPLE"  (NAME, CATEGORY) VALUES ('D', 'Cat3');
COMMIT;
Following your answer the query should be something like this, but it doesn't work.
SELECT name FROM sample WHERE category='Cat1' AND NOT EXISTS (SELECT name FROM sample WHERE category='Cat2');






Re: Finding entries missing a value and listing all used values for specific data [message #681998 is a reply to message #681997] Wed, 23 September 2020 15:35 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Set operators might be another option.

SQL> select name from sample where category = 'Cat1'
  2  minus
  3  select name from sample where category = 'Cat2';

NAME
--------------------
C

SQL> select name, listagg(category, ', ') within group (order by category) cat
  2  from sample
  3  where name in (select name from sample where category = 'Cat1'
  4                 minus
  5                 select name from sample where category = 'Cat2')
  6  group by name;

NAME                 CAT
-------------------- --------------------
C                    Cat1, Cat3

SQL>
Do the vice versa part yourself.
Re: Finding entries missing a value and listing all used values for specific data [message #681999 is a reply to message #681997] Thu, 24 September 2020 00:18 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
magriii wrote on Wed, 23 September 2020 22:21
...
Following your answer the query should be something like this, but it doesn't work.
SELECT name FROM sample WHERE category='Cat1' AND NOT EXISTS (SELECT name FROM sample WHERE category='Cat2');

You have to correlate the rows between the 2 query parts:
SQL> SELECT name FROM sample a
  2  WHERE category='Cat1'
  3    AND NOT EXISTS (SELECT name FROM sample b WHERE category='Cat2' AND b.name = a.name);
NAME
--------------------
C

[Updated on: Thu, 24 September 2020 00:22]

Report message to a moderator

Re: Finding entries missing a value and listing all used values for specific data [message #682000 is a reply to message #681999] Thu, 24 September 2020 00:52 Go to previous messageGo to next message
magriii
Messages: 3
Registered: September 2020
Junior Member
This helps.
Thank you two for the quick help.
Re: Finding entries missing a value and listing all used values for specific data [message #682002 is a reply to message #681997] Thu, 24 September 2020 07:52 Go to previous message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
Just as a side observation .. do not - ever - use double-quotes around table and column names. That creates case-sensitive names that are a pain in the ***.

SQL> -- create and populate test table
SQL> create table "my_test" ("dob" date);

Table created.

SQL> insert into my_test values (sysdate);
insert into my_test values (sysdate)
            *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> insert into "my_test" values (sysdate);

1 row created.

SQL> select * from my_test;
select * from my_test
              *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> select * from "my_test";

dob
---------
24-SEP-20

1 row selected.

SQL> -- clean up the test
SQL> drop table my_test purge;
drop table my_test purge
           *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> drop table "my_test" purge;

Table dropped.

SQL> -- another example - notice the case in which I am creating the names
SQL> create table mytest_upper (dob date);

Table created.

SQL> create table "mytest_lower" (dob date);

Table created.

SQL> select table_name
  2  from user_tables
  3  where upper(table_name) like 'MYTEST%';

TABLE_NAME
--------------------
mytest_lower
MYTEST_UPPER

2 rows selected.

SQL> drop table mytest_upper purge;

Table dropped.

SQL> drop table "mytest_lower" purge;

Table dropped.

SQL> 
SQL> spo off
In your case it would have all worked because you created the names in upper-case, which is what oracle does of you don't enclose in double quotes. But it is such a terrible habit to get into.
Previous Topic: Query help on 5 tables.
Next Topic: How to add SOAP elements to XML
Goto Forum:
  


Current Time: Thu Mar 28 06:42:12 CDT 2024