Home » SQL & PL/SQL » SQL & PL/SQL » Selecting records which dont have referencing records (oracle database 9i,9.2.08,XP)
Selecting records which dont have referencing records [message #377818] Thu, 25 December 2008 13:26 Go to next message
yoonus.it@gmail.com
Messages: 109
Registered: June 2007
Location: kuwait
Senior Member

I have a table with data in the following format

rh_sysid	rh_txn_type	rh_ref_sys_id	rh_ref_txn_cod

800		ssmr			
801		sspr		800		ssmr
810		ssmr				
811		ssmr		
812		ssmr		
813		sspr		812		ssmr


I want to get only records which has ssmr without sspr

result i am expecting is

rh_sysid	rh_txn_type	rh_ref_sys_id	rh_ref_txn_cod

810		ssmr
811		ssmr


when i use self non equal join its giving me result but it will produce cartesain join giving me many rows. 
I am looking at bringing down the cost of a query.

Looking forward to your advising

[Updated on: Thu, 25 December 2008 13:31] by Moderator

Report message to a moderator

Re: Selecting records which dont have referencing records [message #377820 is a reply to message #377818] Thu, 25 December 2008 13:35 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
The solution depends on the specifications of your data.
What are the different cases you can have?

Post a Test case: create table and insert statements along with the result you want with these data.
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Use the "Preview Message" button to verify.

Regards
Michel
Re: Selecting records which dont have referencing records [message #377825 is a reply to message #377818] Thu, 25 December 2008 15:09 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
* Depending on the data provided...


oli@ORAFAQ.US.ORACLE.COM> select * from test_377818;

RH_SY RH_TX RH_RE RH_RE
----- ----- ----- -----
800   ssmr
801   sspr  800   ssmr
810   ssmr
811   ssmr
812   ssmr
813   sspr  812   ssmr

6 rows selected.

oli@ORAFAQ.US.ORACLE.COM> ed
Wrote file afiedt.buf

  1  select a.rh_sysid,a.rh_txn_type,a.rh_ref_sys_id,a.rh_ref_txn_cod
  2    from test_377818 a where a.rh_sysid not in
  3* (select b.rh_ref_sys_id from test_377818 b where b.rh_ref_sys_id is not null ) and a.rh_txn_type<>'sspr' 
oli@ORAFAQ.US.ORACLE.COM> /

RH_SY RH_TX RH_RE RH_RE
----- ----- ----- -----
810   ssmr
811   ssmr




Try modifying the query....




Regards,
Oli

[Updated on: Thu, 25 December 2008 15:51]

Report message to a moderator

Re: Selecting records which dont have referencing records [message #377924 is a reply to message #377825] Fri, 26 December 2008 06:54 Go to previous messageGo to next message
yoonus.it@gmail.com
Messages: 109
Registered: June 2007
Location: kuwait
Senior Member
Michel please see the test case down as you have pointed. 

create table ot_req (rh_sysid NUMBER(4),rh_txn_type VARCHAR2(5),rh_ref_sys_id NUMBER(4),rh_ref_txn_cod VARCHAR2(5));

INSERT INTO ot_req  VALUES(800,'SSMR',NULL,NULL);
INSERT INTO ot_req  VALUES(801,'SSPR',800,'SSMR');
INSERT INTO ot_req  VALUES(810,'SSMR',NULL,NULL);
INSERT INTO ot_req  VALUES(811,'SSMR',NULL,NULL);
INSERT INTO ot_req  VALUES(812,'SSMR',NULL,NULL);
INSERT INTO ot_req  VALUES(813,'SSPR',812,'SSMR');
INSERT INTO ot_req  VALUES(814,'SSMR',NULL,NULL);
INSERT INTO ot_req  VALUES(815,'SSPR',814,'SSMR');


SELECT
       rh_sysid
FROM
       ot_req 
WHERE
      rh_sysid   
NOT IN             
      (SELECT 
          rh_ref_sys_id  
        FROM 
          ot_req 
        WHERE 
          rh_ref_sys_id IS NOT NULL)
AND 
      rh_txn_type!='SSPR';

RH_SYSID
810
811

I can get result but my issue is to bring down the cost. This table has more than 50000 rows. 
Re: Selecting records which dont have referencing records [message #377925 is a reply to message #377924] Fri, 26 December 2008 07:09 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
You didn't tell if you test case is the only possible.
Can a row have several children?
Can a row have a child with same type?
Is parent always ssmr?
Is child always sspr?
Is there other type?
...

Regards
Michel
Re: Selecting records which dont have referencing records [message #377926 is a reply to message #377925] Fri, 26 December 2008 07:41 Go to previous messageGo to next message
yoonus.it@gmail.com
Messages: 109
Registered: June 2007
Location: kuwait
Senior Member
No this is not the only case.
yes 1 row can have one or many children.
same child type is not possible.
parent can be any ending with mr.
child will always be ending with pr.

mr pr relation as follows
         ssmr sspr
         qamr qapr
         admr adpr 
Re: Selecting records which dont have referencing records [message #377934 is a reply to message #377926] Fri, 26 December 2008 08:47 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
If a parent is "ssmr" then all children are "sspr", is this true?
What is the number of rows?
What is the percentage of parents?
What is the percentage of parents that have no child?
What are the indexes?

Regards
Michel
Re: Selecting records which dont have referencing records [message #377959 is a reply to message #377934] Fri, 26 December 2008 23:09 Go to previous messageGo to next message
yoonus.it@gmail.com
Messages: 109
Registered: June 2007
Location: kuwait
Senior Member
Yes If a parent is "ssmr" then all children are "sspr"
Total number of rows = 71503
Percentage of parents = 13 % of total rows
Percentage of parents that have no child = 7 % of total rows

Index

Unique index on rh_sysid
Non unique rh_ref_sys_id
Non unique index on rh_txn_type
Non unique index on rh_ref_txn_cod

Regards Smile
uns
Re: Selecting records which dont have referencing records [message #377962 is a reply to message #377959] Sat, 27 December 2008 01:26 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
select rh_sysid from ot_req where substr(rh_txn_type,3) = 'mr'
minus
select rh_ref_sys_id from ot_req 

Add an index on (substr(rh_txn_type,3),rh_sysid).
I don't know the other queries you execute but an index on each column is doubtful.
In addition if parent type always ends with 'mr' and child with 'pr' and parent and child always have the same first 2 characters, this means you shoud have 2 fields and not one.

Regards
Michel

[Updated on: Sat, 27 December 2008 01:30]

Report message to a moderator

Re: Selecting records which dont have referencing records [message #377998 is a reply to message #377818] Sat, 27 December 2008 12:00 Go to previous messageGo to next message
yoonus.it@gmail.com
Messages: 109
Registered: June 2007
Location: kuwait
Senior Member
i will try this way. I did not get Michel what you mean "this means you shoud have 2 fields and not one."
Re: Selecting records which dont have referencing records [message #377999 is a reply to message #377998] Sat, 27 December 2008 12:16 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
See Normalization.
If your field contains 2 parts, it means it not a relational attribute it is 2 attributes: the first part that define the real type and the second part that defines if it is a parent or a child (a subtype if you want).
So there should be 2 columns.
More as the second part can be entirely defined by the fact that rh_ref_sys_id is null or not, the second part is not a real value.

Regards
Michel

[Updated on: Sat, 27 December 2008 12:16]

Report message to a moderator

Re: Selecting records which dont have referencing records [message #378489 is a reply to message #377818] Tue, 30 December 2008 12:45 Go to previous messageGo to next message
yoonus.it@gmail.com
Messages: 109
Registered: June 2007
Location: kuwait
Senior Member
Thank You very much Michel. I have added an index on that column as you said and found a great difference in cost. I am bit weak in normalization i realized that after i read that link from you. I am happy if you could give me some links on normalization if you have such.

Thank You
uns
Re: Selecting records which dont have referencing records [message #378491 is a reply to message #378489] Tue, 30 December 2008 12:54 Go to previous message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
I have none, I learned db desogn in a time web did not exist bit I think you can easily find links googling for "database normalization".

Regards
Michel
Previous Topic: dates and null
Next Topic: Deleting all child table records with single DELETE statement
Goto Forum:
  


Current Time: Fri Feb 14 18:51:58 CST 2025