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:
- I want to find all ID columns that have one and only one TXT that equals 'HELLO6'. I.e ID = 4.
- 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
Join the conversationComment
Share
Comments
Results
Contribute to the conversation