Q

SQL query for sorting through ID columns

A reader asks Oracle expert Karen Morton how to query a group of rows based on their text columns.

There are more tables, columns and attributes in the scenario and redesign of schema is not an option for now. Hope you have time to share some hints on this.

create table my_master (id number,ref number);
create table my_detail (id number,txt varchar2(8));
insert into my_master (id,ref) values (1,1);
insert into my_master (id,ref) values (1,2);
insert into my_master (id,ref) values (1,3);
insert into my_master (id,ref) values (2,1);
insert into my_master (id,ref) values (2,4);
insert into my_master (id,ref) values (2,5);
insert into my_master (id,ref) values (3,6);
insert into my_master (id,ref) values (3,7);
insert into my_master (id,ref) values (4,7);

insert into my_detail (id,txt) values (1,'HELLO1');
insert into my_detail (id,txt) values (2,'HELLO2');
insert into my_detail (id,txt) values (3,'HELLO3');
insert into my_detail (id,txt) values (4,'HELLO4');
insert into my_detail (id,txt) values (5,'HELLO5');
insert into my_detail (id,txt) values (6,'HELLO5');
insert into my_detail (id,txt) values (7,'HELLO6');

SQL> select t1.id,t1.ref,t2.txt from my_master t1,my_detail t2 where t1.ref = t2.id;


       ID        REF TXT
---------- ---------- --------
        1          1 HELLO1
        1          2 HELLO2
        1          3 HELLO3
        2          1 HELLO1
        2          4 HELLO4
        2          5 HELLO5
        3          6 HELLO5
        3          7 HELLO6
        4          7 HELLO6
 

9 rows selected.
 
Now I want to write a query that can solve the following:

  1. I want to find all ID columns that have one and only one TXT that equals 'HELLO6'. I.e ID = 4.
  2. I want to find all ID columns that where TXT='HELLO5' AND 'HELLO6'. I.e ID = 3

There are several ways to get the results you wish, but here are two relatively straightforward ways that hopefully will work if I understood your requirements properly. Also note that you'd want to have an index on my_detail(ref, txt) to make the inner queries perform the best and certainly you'd want to have an appropriate index on my_master as well (a primary key or other).

1) I want to find all ID columns that have one and only one TXT that equals 'HELLO6'. I.e ID = 4.

SQL> select id
 2  from my_master
 3  where id in
 4  (
 5  select t1.id
 6  from my_master t1, my_detail t2
 7  where t1.ref = t2.id
 8  and t2.txt = 'HELLO6'
 9  )
10  group by id
11  having count(*) = 1;

            ID
---------------
             4
                       
2) I want to find all ID columns that where TXT='HELLO5' AND 'HELLO6'. I.e ID = 3.
             
SQL> select id
 2  from
 3  (
 4  select t1.id
 5  from my_master t1, my_detail t2
 6  where t1.ref = t2.id
 7  and t2.txt IN ('HELLO5', 'HELLO6')
 8  )
 9  group by id
10  having count(*) = 2 ;

            ID
---------------
             3

This was first published in October 2011

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close