Home » SQL & PL/SQL » SQL & PL/SQL » Complex SQL Challenge! Multiple conditional joins!
Complex SQL Challenge! Multiple conditional joins! [message #34397] |
Thu, 09 December 2004 05:56 |
Mark Tudor
Messages: 2 Registered: December 2004
|
Junior Member |
|
|
Hi Guys,
If any of you can solve this I'd be chuffed to mintballs! Lol.
Tables (illustration not actual!)
-----------------------------
rule_term (element1, etype1, element2, etype2, name)
square (element_id, name)
circle (element_id, name)
The rule_term table links two elements, which can be either a circle or a square. This gives four combinations which can be linked (S - S, S - C, C - S, S - S).
The type of the element is defined in the corresponding etype field.
The Problem
------------
We need to select all rule_terms and, for each, return the name of both elements, along with the name of the rule.
This raises issues as we can't simply do a join because we do not know where this join should go as it varies depending upon the value of the corresponding etype field.
We need a way to perform a conditional join or to, in some other way, retrieve the name for each element from the correct table depending upon the etype field.
The Current Solution
--------------------
Currently we are performing four SQL SELECT statements, one for each combination of objects, and UNIONing the results. This seems rather inefficient and I'd love to find a better way.
Please bear in mind that we cannot change the structure of the database and this must be done in SQL.
Would be really grateful for any help!
Thanks,
Mark.
|
|
|
Re: Complex SQL Challenge! Multiple conditional joins! [message #34398 is a reply to message #34397] |
Thu, 09 December 2004 06:54 |
Art Metzer
Messages: 2480 Registered: December 2002
|
Senior Member |
|
|
SQL> CREATE TABLE rule_term (
2 element1 NUMBER
3 , etype1 VARCHAR2(6)
4 , element2 NUMBER
5 , etype2 VARCHAR2(6)
6 , name VARCHAR2(15)
7 )
8 /
Table created.
SQL> CREATE TABLE square (
2 element_id NUMBER
3 , name VARCHAR2(15)
4 )
5 /
Table created.
SQL> CREATE TABLE circle (
2 element_id NUMBER
3 , name VARCHAR2(15)
4 )
5 /
Table created.
SQL> INSERT INTO square VALUES (144,'SQ A');
SQL> INSERT INTO square VALUES (900,'SQ B');
SQL> INSERT INTO square VALUES (49,'SQ C');
SQL> INSERT INTO square VALUES (1296,'SQ D');
SQL> INSERT INTO square VALUES (1,'SQ E');
SQL> INSERT INTO circle VALUES (3,'CIR V');
SQL> INSERT INTO circle VALUES (14,'CIR W');
SQL> INSERT INTO circle VALUES (15,'CIR X');
SQL> INSERT INTO circle VALUES (92,'CIR Y');
SQL> INSERT INTO circle VALUES (6,'CIR Z');
SQL> INSERT INTO rule_term VALUES (144,'SQUARE',900,'SQUARE','TWO SQUARES');
SQL> INSERT INTO rule_term VALUES (3,'CIRCLE',49,'SQUARE','1 CIR, 1 SQ');
SQL> INSERT INTO rule_term VALUES (1296,'SQUARE',14,'CIRCLE','1 SQ, 1 CIR');
SQL> INSERT INTO rule_term VALUES (15,'CIRCLE',92,'CIRCLE','TWO CIRCLES');
SQL> INSERT INTO rule_term VALUES (1,'SQUARE',6,'CIRCLE','1 SQ, 1 CIR');
SQL> COL element1_type FORMAT A13
SQL> COL element2_type FORMAT A13
SQL> SELECT x.rt_name rule_term_name
2 , x.element1_id element1_id
3 , x.element1_type element1_type
4 , x.element1_name element1_name
5 , x.element2_id element2_id
6 , x.element2_type element2_type
7 , x.element2_name element2_name
8 FROM (SELECT a.rt_name
9 , a.rt_rowid
10 , MAX(DECODE(a.r,1,a.eid)) element1_id
11 , MAX(DECODE(a.r,1,a.etype)) element1_type
12 , MAX(DECODE(a.r,1,b.name)) element1_name
13 , MAX(DECODE(a.r,2,a.eid)) element2_id
14 , MAX(DECODE(a.r,2,a.etype)) element2_type
15 , MAX(DECODE(a.r,2,b.name)) element2_name
16 FROM (SELECT dblr.r
17 , DECODE(dblr.r
18 , 1, rt.etype1
19 , 2, rt.etype2) etype
20 , DECODE(dblr.r
21 , 1, rt.element1
22 , 2, rt.element2) eid
23 , rt.ROWID rt_rowid
24 , rt.name rt_name
25 FROM rule_term rt
26 , (SELECT 1 r
27 FROM DUAL
28 UNION ALL
29 SELECT 2 r
30 FROM DUAL) dblr) a
31 , (SELECT s.element_id eid
32 , s.name name
33 , 'SQUARE' etype
34 FROM square s
35 UNION ALL
36 SELECT c.element_id eid
37 , c.name name
38 , 'CIRCLE' etype
39 FROM circle c) b
40 WHERE a.etype = b.etype
41 AND a.eid = b.eid
42 GROUP BY a.rt_name
43 , a.rt_rowid) x
44 /
RULE_TERM_NAME ELEMENT1_ID ELEMENT1_TYPE ELEMENT1_NAME ELEMENT2_ID ELEMENT2_TYPE ELEMENT2_NAME
--------------- ----------- ------------- --------------- ----------- ------------- ---------------
1 CIR, 1 SQ 3 CIRCLE CIR V 49 SQUARE SQ C
1 SQ, 1 CIR 1296 SQUARE SQ D 14 CIRCLE CIR W
1 SQ, 1 CIR 1 SQUARE SQ E 6 CIRCLE CIR Z
TWO CIRCLES 15 CIRCLE CIR X 92 CIRCLE CIR Y
TWO SQUARES 144 SQUARE SQ A 900 SQUARE SQ B
SQL>
|
|
|
|
Re: Complex SQL Challenge! Multiple conditional joins! [message #34400 is a reply to message #34397] |
Thu, 09 December 2004 07:33 |
Jon
Messages: 483 Registered: May 2001
|
Senior Member |
|
|
I have no idea what "chuffed to mintballs" means, but anyway, here is my solution. I have little doubt that Art's works (probably more sure than mine does in all cases - lol), so you may have a choice which some testing and performance checking should help you make.
select decode(e1_type, 'S', s1_name, c1_name) First,
decode(e2_type, 'S', s2_name, c2_name) Second,
name
from
(select e1_id ,e1_type ,e2_id ,e2_type ,name, s1.s_id s1_id, s1.s_name s1_name, c1.c_id c1_id, c1.c_name c1_name,
s2.s_id s2_id, s2.s_name s2_name, c2.c_id c2_id, c2.c_name c2_name
from t1,
s1,
c1,
s1 s2,
c1 c2
where t1.e1_id = s1.s_id (+)
and t1.e1_id = c1.c_id(+)
and t1.e2_id = s2.s_id(+)
and t1.e2_id = c2.c_id)
;
Here is the test I ran:
SQL> drop table t1;
Table dropped.
SQL> create table t1 (e1_id number, e1_type varchar2(1), e2_id number, e2_type varchar2(1), name var
char2(10));
Table created.
SQL> insert into t1 values(1,'S',1,'C','S 1 - C 1');
1 row created.
SQL> insert into t1 values(1,'C',1,'S','C 1 - S 1');
1 row created.
SQL> insert into t1 values(1,'S',2,'S','S 1 - S 2');
1 row created.
SQL> insert into t1 values(1,'C',2,'C','C 1 - C 2');
1 row created.
SQL> insert into t1 values(2,'C',1,'S','C 2 - S 1');
1 row created.
SQL> insert into t1 values(3,'C',1,'S','C 3 - S 1');
1 row created.
SQL> drop table s1;
Table dropped.
SQL> create table s1 (s_id number, s_name varchar2(10));
Table created.
SQL> insert into s1 values(1,'S 1');
1 row created.
SQL> insert into s1 values(2,'S 2');
1 row created.
SQL> drop table c1;
Table dropped.
SQL> create table c1 (c_id number, c_name varchar2(10));
Table created.
SQL> insert into c1 values(1,'C 1');
1 row created.
SQL> insert into c1 values(2,'C 2');
1 row created.
SQL> insert into c1 values(3,'C 3');
1 row created.
SQL>
SQL> select decode(e1_type, 'S', s1_name, c1_name) First,
2 decode(e2_type, 'S', s2_name, c2_name) Second,
3 name
4 from
5 (select e1_id ,e1_type ,e2_id ,e2_type ,name, s1.s_id s1_id, s1.s_name s1_name, c1.c_id c1_id,
c1.c_name c1_name,
6 s2.s_id s2_id, s2.s_name s2_name, c2.c_id c2_id, c2.c_name c2_name
7 from t1,
8 s1,
9 c1,
10 s1 s2,
11 c1 c2
12 where t1.e1_id = s1.s_id (+)
13 and t1.e1_id = c1.c_id(+)
14 and t1.e2_id = s2.s_id(+)
15 and t1.e2_id = c2.c_id)
16 ;
FIRST SECOND NAME
---------- ---------- ----------
S 1 C 1 S 1 - C 1
C 1 S 1 C 1 - S 1
S 1 S 2 S 1 - S 2
C 1 C 2 C 1 - C 2
C 2 S 1 C 2 - S 1
C 3 S 1 C 3 - S 1
6 rows selected.
|
|
|
Re: Complex SQL Challenge! Multiple conditional joins! [message #34413 is a reply to message #34397] |
Fri, 10 December 2004 05:07 |
Padders
Messages: 79 Registered: January 2004
|
Member |
|
|
Another version...
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> CREATE OR REPLACE VIEW element
2 AS
3 SELECT 'CIRCLE' element_type, element_id, name
4 FROM circle
5 UNION ALL
6 SELECT 'SQUARE', element_id, name
7 FROM square;
View created.
SQL> SELECT r.name, e1.name e1_name, e2.name e2_name
2 FROM rule_term r, element e1, element e2
3 WHERE e1.element_id = r.element1
4 AND e1.element_type = r.etype1
5 AND e2.element_id = r.element2
6 AND e2.element_type = r.etype2;
NAME E1_NAME E2_NAME
--------------- --------------- ---------------
1 SQ, 1 CIR SQ D CIR W
TWO CIRCLES CIR X CIR Y
1 SQ, 1 CIR SQ E CIR Z
TWO SQUARES SQ A SQ B
1 CIR, 1 SQ CIR V SQ C
SQL>
Perhaps the use of UNION ALL in the view may prevent index paths. See how you go.
|
|
|
Re: Complex SQL Challenge! Multiple conditional joins! [message #34414 is a reply to message #34397] |
Fri, 10 December 2004 06:10 |
Mark Tudor
Messages: 2 Registered: December 2004
|
Junior Member |
|
|
You guys rock ;-)
You've given me a completely different viewpoint on this problem and the query which was taking about 4 seconds to execute is now taking about 0.1 seconds!
I'm still tinkering so I'll try and let you guys know how it goes.
Thanks! I can truly say I'm chuffed to mintballs! ;-)
Mark.
|
|
|
Goto Forum:
Current Time: Fri Mar 29 08:26:18 CDT 2024
|