1

I have following two tables in Oracle 8i

For each col1 in T1 table, we need to find out the number of occurrences in T2 table (for matching col3).

QUESTION

What is the best way to achieve this in Oracle using a single query (without creating temporary table)?

Fiddle

EXPECTED RESULT

‘C1’ -- 10 -- 1

‘C1’ -- 8 -- 1

‘C2’ -- 10 -- 1

‘C3’ -- 10 -- 0

‘C4’ -- 10 -- 2

SQL

--Table 1
CREATE TABLE T1 (col1 varchar2(2), col2 varchar(8), col3 NUMBER);

INSERT INTO T1  (col1, col2, col3) VALUES  ('C1', 'john',10);
INSERT INTO T1  (col1, col2, col3) VALUES ('C1', 'nishal',8);
INSERT INTO T1  (col1, col2, col3) VALUES  ('C2', 'piers',10);
INSERT INTO T1  (col1, col2, col3) VALUES  ('C3', 'sara',10);
INSERT INTO T1  (col1, col2, col3) VALUES  ('C4', 'lijo',10);


--Table 2

CREATE TABLE T2 (col1 varchar2(2), col2 varchar(8), col3 NUMBER);

INSERT INTO T2  (col1, col2 , col3) VALUES ('R0', 'C1,C4',10);
INSERT INTO T2  (col1, col2 , col3) VALUES ('R1', 'C1',8);
INSERT INTO T2  (col1, col2 , col3) VALUES ('R2', 'C2,C4',10);

Fiddle

LCJ
  • 22,196
  • 67
  • 260
  • 418

1 Answers1

2
select  T1.col1
,       T1.col3
,       count(T2.col1)
from    T1
left join
        T2
on      T1.col3 = T2.col3
        and ',' || T2.col2 || ',' like '%,' || T1.col1 || ',%'
group by
        T1.col1
,       T1.col3

Example at SQL Fiddle.

Andomar
  • 232,371
  • 49
  • 380
  • 404