Home » SQL & PL/SQL » SQL & PL/SQL » Intersection of a large, unknown number of sets (Database 10.2.0.4.0)
Intersection of a large, unknown number of sets [message #440557] Mon, 25 January 2010 11:28 Go to next message
lydianblues
Messages: 5
Registered: January 2010
Junior Member
A somewhat theoretical challenge:

Given a table TBL with two integer columns COL1 and COL2, any integer X determines a set SET(X) given by:

SELECT COL2 from TBL WHERE COL1 = X;

and given a collection of X values X1, X2, ..., Xn where the number of values is not known until run time, write a single query that computes: SET(X1) INTERSECT SET(X2) ... INTERSECT SET(Xn)

I'd rather not use the INTERSECTION operation itself in case I have to port this to MySQL. Currently I have a CASE statement that has 50 cases, one case for each value of n. The nth case looks like:

query := "SELECT COL2 from TBL WHERE COL1 = :X1 INTERSECT
SELECT COL2 from TBL WHERE COL1 = :X2 INTERSECT
...
SELECT COL2 from TBL WHERE COL1 = :Xn"

EXECUTE IMMEDIATE query BULK COLLECT INTO <some varray> USING
X1, X2, ... Xn.

This is horrible. It makes the PL/SQL procedure hundreds of lines long. Surely there is a better way???
Re: Intersection of a large, unknown number of sets [message #440559 is a reply to message #440557] Mon, 25 January 2010 11:30 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
It would be helpful if you provided DDL for tables involved.
It would be helpful if you provided DML for test data.
It would be helpful if you provided expected/desired results & a detailed explanation how & why the test data gets transformed or organized.
Re: Intersection of a large, unknown number of sets [message #440571 is a reply to message #440559] Mon, 25 January 2010 16:56 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
To avoid the SQL becoming large and complicated, why not insert the integers X into a table.

(note that all of this is untested code - use it as a guide)
CREATE TABLE ky (
 X NUMBER PRIMARY KEY
);

INSERT INTO ky VALUES (&a);
INSERT INTO ky VALUES (&b);
INSERT INTO ky VALUES (&c);


Then we are able to join this new table to the other table
SELECT DISTINCT tbl.col2, ky.x
FROM   tbl
JOIN   ky ON tbl.col1 = ky.x


This gives us - for each value of COL2 - a list of the sets in which that value is found. Now it is just a matter of finding the values that appear in every set.
SELECT s.col2
FROM (
  SELECT DISTINCT tbl.col2, ky.x
  FROM   tbl
  JOIN   ky ON tbl.col1 = ky.x
) s
CROSS JOIN (
  SELECT COUNT(*) AS cnt FROM ky
) c
GROUP BY s.col2
HAVING c.cnt = COUNT(*)


Ross Leishman
Previous Topic: full outer join or union
Next Topic: matrix correlation
Goto Forum:
  


Current Time: Thu Sep 29 17:33:57 CDT 2016

Total time taken to generate the page: 0.15579 seconds