Finding entries missing a value and listing all used values for specific data [message #681995] |
Wed, 23 September 2020 09:29 |
|
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 |
|
Michel Cadot
Messages: 68694 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 |
|
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 #681999 is a reply to message #681997] |
Thu, 24 September 2020 00:18 |
|
Michel Cadot
Messages: 68694 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 #682002 is a reply to message #681997] |
Thu, 24 September 2020 07:52 |
|
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.
|
|
|