Home » SQL & PL/SQL » SQL & PL/SQL » need help on query
need help on query [message #211345] Thu, 28 December 2006 01:48 Go to next message
frank.svs
Messages: 162
Registered: February 2006
Senior Member
Hi frns,

I need some help regarding a query.

Problem: i need to display the names of the recommding authority and sanctioning authority for every employee.

Method-1 : This is absolutely working fine
SQL> -- my logic after normalizing
SQL> create table temp
2 (id number,
3 name varchar(20),
4 recid number,
5 sancid number
6 );

Table created.

SQL>
SQL> insert into temp values (101,'srini',null,null) ;

1 row created.

SQL> insert into temp values (102,'rk',null,null);

1 row created.

SQL> insert into temp values (103,'ravi',102,101);

1 row created.

SQL> insert into temp values (104,'manu',103,102);

1 row created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select a.id,a.name,a.recid,b.name,a.sancid,c.name from temp a,temp b,temp c
2 where a.recid=b.id
3 and a.sancid=c.id;

ID NAME RECID NAME sancid name
--------- --------------------
104 manu 103 ravi 102 rk




Method-2: Poor Database Design
SQL> -- again see the old logic

But i have the database something as follows

create table t1
(id number,
name varchar(20)
);
insert into t1 values (101,'srini');
insert into t1 values (102,'rk');
insert into t1 values (103,'ravi');
insert into t1 values (104,'manu') ;

create table t2
(
sno number,
id number,
recid number,
sancid number
);
insert into t2 values(1,104,103,101);
insert into t2 values(1,103,102,101);
commit;


But i am trying to get the same above output as in METHOD-1 i.e who is the recommending authority and who is the sanctioning authority for every employee. Can anyone tell what's wrong in the following query

SQL> select x.id,x.name,x.recid,y.name,x.sancid,z.name
from
(select a.id id,a.name name,b.recid recid,b.sancid sancid from t1 a,t2 b where a.id=b.id) x,
(select a.id id,a.name name,b.recid recid,b.sancid sancid from t1 a,t2 b where a.id=b.id) y,
(select a.id id,a.name name,b.recid recid,b.sancid sancid from t1 a,t2 b where a.id=b.id) z
where x.recid=y.id
and x.sancid=z.id

SQL>
SQL>
SQL> /

no rows selected

Why i am not getting the output. Can anyone help me out.


Regards,
franky
Re: need help on query [message #211404 is a reply to message #211345] Thu, 28 December 2006 07:45 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
139 messages and you still don't know how to use code tags?
Re: need help on query [message #211408 is a reply to message #211404] Thu, 28 December 2006 07:57 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
... and still dumping each and every question in the Expert forum
Re: need help on query [message #211418 is a reply to message #211345] Thu, 28 December 2006 09:41 Go to previous messageGo to next message
amul
Messages: 252
Registered: April 2001
Location: Chennai
Senior Member
shouldnt you have used a t3 and a 'c' condition?you dont include that as a join condition but expect an output?
Re: need help on query [message #211440 is a reply to message #211345] Thu, 28 December 2006 12:54 Go to previous message
Barbara Boehmer
Messages: 8621
Registered: November 2002
Location: California, USA
Senior Member
Your data in temp does not match your query result and your data in t2 does not match your data in temp. In the example below, I changed one of the 101 to 102 in t2 to match your temp data.

I see that you are trying to substitute a join of t1 and t2 to replace temp in your original working query. The only problem is that you used an inner join instead of an outer join, so it did not include all of the rows.

You can simplify things by using the subquery factoring clause or better yet by just doing self joins using t1 and t2 without using inline views as in the last query below.

SCOTT@10gXE> -- test data:
SCOTT@10gXE> SELECT * FROM temp
  2  /

        ID NAME                      RECID     SANCID
---------- -------------------- ---------- ----------
       101 srini
       102 rk
       103 ravi                        102        101
       104 manu                        103        102

SCOTT@10gXE> SELECT * FROM t1
  2  /

        ID NAME
---------- --------------------
       101 srini
       102 rk
       103 ravi
       104 manu

SCOTT@10gXE> SELECT * FROM t2
  2  /

       SNO         ID      RECID     SANCID
---------- ---------- ---------- ----------
         1        104        103        102
         1        103        102        101

SCOTT@10gXE> -- original query on temp:
SCOTT@10gXE> select a.id, a.name, a.recid, b.name, a.sancid, c.name
  2  from   temp a, temp b, temp c
  3  where  a.recid  = b.id
  4  and    a.sancid = c.id;

        ID NAME                      RECID NAME                     SANCID NAME
---------- -------------------- ---------- -------------------- ---------- --------------------
       103 ravi                        102 rk                          101 srini
       104 manu                        103 ravi                        102 rk

SCOTT@10gXE> -- original query on t1 and t2:
SCOTT@10gXE> select x.id,x.name,x.recid,y.name,x.sancid,z.name
  2  from
  3  (select a.id id,a.name name,b.recid recid,b.sancid sancid from t1 a,t2 b where a.id=b.id) x,
  4  (select a.id id,a.name name,b.recid recid,b.sancid sancid from t1 a,t2 b where a.id=b.id) y,
  5  (select a.id id,a.name name,b.recid recid,b.sancid sancid from t1 a,t2 b where a.id=b.id) z
  6  where x.recid=y.id
  7  and x.sancid=z.id;

no rows selected

SCOTT@10gXE> -- your inline views joining t1 and t2 do not include all the rows in temp:
SCOTT@10gXE> select a.id id,a.name name,b.recid recid,b.sancid sancid from t1 a,t2 b where a.id=b.id;

        ID NAME                      RECID     SANCID
---------- -------------------- ---------- ----------
       103 ravi                        102        101
       104 manu                        103        102

SCOTT@10gXE> -- if you add an outer join, the result is the same as temp:
SCOTT@10gXE> select a.id id,a.name name,b.recid recid,b.sancid sancid from t1 a,t2 b where a.id=b.id(+);

        ID NAME                      RECID     SANCID
---------- -------------------- ---------- ----------
       104 manu                        103        102
       103 ravi                        102        101
       102 rk
       101 srini

SCOTT@10gXE> -- so adding the outer join produces the same results:
SCOTT@10gXE> select x.id,x.name,x.recid,y.name,x.sancid,z.name
  2  from
  3  (select a.id id,a.name name,b.recid recid,b.sancid sancid from t1 a,t2 b where a.id=b.id(+)) x,
  4  (select a.id id,a.name name,b.recid recid,b.sancid sancid from t1 a,t2 b where a.id=b.id(+)) y,
  5  (select a.id id,a.name name,b.recid recid,b.sancid sancid from t1 a,t2 b where a.id=b.id(+)) z
  6  where x.recid=y.id
  7  and x.sancid=z.id;

        ID NAME                      RECID NAME                     SANCID NAME
---------- -------------------- ---------- -------------------- ---------- --------------------
       104 manu                        103 ravi                        102 rk
       103 ravi                        102 rk                          101 srini

SCOTT@10gXE> -- could be simplified, using subquery factoring clause
SCOTT@10gXE> -- and eliminating unnecessary aliases as:
SCOTT@10gXE> WITH substitute_for_temp AS
  2    (SELECT t1.id, t1.name, t2.recid, t2.sancid
  3  	FROM   t1, t2
  4  	WHERE  t1.id = t2.id (+))
  5  SELECT x.id, x.name, x.recid, y.name, x.sancid, z.name
  6  FROM   substitute_for_temp x,
  7  	    substitute_for_temp y,
  8  	    substitute_for_temp z
  9  WHERE  x.recid  = y.id
 10  AND    x.sancid = z.id;

        ID NAME                      RECID NAME                     SANCID NAME
---------- -------------------- ---------- -------------------- ---------- --------------------
       104 manu                        103 ravi                        102 rk
       103 ravi                        102 rk                          101 srini

SCOTT@10gXE> -- or even simpler as:
SCOTT@10gXE> SELECT t1x.id, t1x.name, t2x.recid, t1y.name, t2x.sancid, t1z.name
  2  FROM   t1 t1x, t2 t2x, t1 t1y, t1 t1z
  3  WHERE  t1x.id     = t2x.id
  4  AND    t2x.recid  = t1y.id
  5  AND    t2x.sancid = t1z.id;

        ID NAME                      RECID NAME                     SANCID NAME
---------- -------------------- ---------- -------------------- ---------- --------------------
       103 ravi                        102 rk                          101 srini
       104 manu                        103 ravi                        102 rk

SCOTT@10gXE> 


Previous Topic: Queryoutput from 2 tables
Next Topic: How to display records by sets of 3 interval (total 15)
Goto Forum:
  


Current Time: Sat Dec 03 03:49:20 CST 2016

Total time taken to generate the page: 0.09310 seconds