Answer

SQL query for sorting through ID 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.

    Requires Free Membership to View


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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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: