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 Go to next message
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 Go to previous messageGo to next message
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 #34399 is a reply to message #34397] Thu, 09 December 2004 07:00 Go to previous messageGo to next message
ramana
Messages: 51
Registered: December 2000
Member
Your question is not clear.
take some sample data and expain.
whats ur data is.
and what do u want in output.

regards,
ramana
Re: Complex SQL Challenge! Multiple conditional joins! [message #34400 is a reply to message #34397] Thu, 09 December 2004 07:33 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
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.
Previous Topic: what is the diffrence between sql%not_found and no_data_found
Next Topic: Verifying table truncation
Goto Forum:
  


Current Time: Fri Mar 29 08:26:18 CDT 2024