Home » SQL & PL/SQL » SQL & PL/SQL » how to compare data with other child data (merged)
how to compare data with other child data (merged) [message #385294] Mon, 09 February 2009 00:30 Go to next message
chaituu
Messages: 115
Registered: June 2008
Senior Member
Relations
********
1 EMP can have multiple EMP_DETAILS.
EMP-DOCUMENT(1-1);

once DOCUMENT is created that reference(ID) will be stored in REF_ID_1 of EMP table.


my requirement is to retrieve those emp records which needs to same COUNTRY_NO in the EMP_DETAILS table and for that employee document should be created.

by above sample data only employees -4&5 should be retrieved.i want to retrieve only emp.* in the select statement.
create table EMP
(
  ID       NUMBER not null,
  NAME     VARCHAR2(15),
  REF_ID_1 NUMBER
);
alter table EMP add constraint ID primary key (ID);
 
create table EMP_DETAILS
(
  ID         NUMBER not null,
  EMP_ID     NUMBER,
  COUNTRY_NO VARCHAR2(15)
);
alter table EMP_DETAILS add constraint PK_EMP_DT primary key (ID);
alter table EMP_DETAILS
  add constraint FK_EMP_DT foreign key (EMP_ID)
  references EMP (ID);
  
create table DOCUMENT
(
  ID   NUMBER not null,
  NAME VARCHAR2(15)
);
alter table DOCUMENT add constraint DOC_PK_ID primary key (ID);
 
sample  data:
 
EMP
*****
ID	NAME	REF_ID_1
1	JOHN	1
2	SCOTT	2
3	MIKE	
4	STEWART 3	
5       HUGHES  4 
 
EMP_DETAILS
************
ID	EMP_ID	COUNTRY_NO
1	1	CNT1
3	2	CNT2
4	3	CNT3
2	2	CNT1
5	3	CNT1
6	3	CNT4
7       4       CNT5
8       5       CNT6
9       5       CNT5
 
DOCUMENT
********
ID	NAME
1	AAAA
2	BBBB
3       CCCC
4       DDDD

[Updated on: Mon, 09 February 2009 01:34] by Moderator

Report message to a moderator

Re: how to compare data with other child data [message #385302 is a reply to message #385294] Mon, 09 February 2009 00:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please post insert statements as well as the result you want for these data.
Also post your version number with 4 decimals.

Regards
Michel
Re: how to compare data with other child data (merged) [message #385308 is a reply to message #385294] Mon, 09 February 2009 01:01 Go to previous messageGo to next message
chaituu
Messages: 115
Registered: June 2008
Senior Member
INSERT INTO EMP ( ID, NAME, REF_ID_1 ) VALUES ( 
1, 'JOHN', 1); 
INSERT INTO EMP ( ID, NAME, REF_ID_1 ) VALUES ( 
2, 'SCOTT', 2); 
INSERT INTO EMP ( ID, NAME, REF_ID_1 ) VALUES ( 
3, 'MIKE', NULL); 
INSERT INTO EMP ( ID, NAME, REF_ID_1 ) VALUES ( 
4, 'STEWART ', 3); 
INSERT INTO EMP ( ID, NAME, REF_ID_1 ) VALUES ( 
5, 'HUGHES', 4); 

INSERT INTO EMP_DETAILS ( ID, EMP_ID, COUNTRY_NO ) VALUES ( 
1, 1, 'CNT1'); 
INSERT INTO EMP_DETAILS ( ID, EMP_ID, COUNTRY_NO ) VALUES ( 
3, 2, 'CNT2'); 
INSERT INTO EMP_DETAILS ( ID, EMP_ID, COUNTRY_NO ) VALUES ( 
4, 3, 'CNT3'); 
INSERT INTO EMP_DETAILS ( ID, EMP_ID, COUNTRY_NO ) VALUES ( 
2, 2, 'CNT1'); 
INSERT INTO EMP_DETAILS ( ID, EMP_ID, COUNTRY_NO ) VALUES ( 
5, 3, 'CNT1'); 
INSERT INTO EMP_DETAILS ( ID, EMP_ID, COUNTRY_NO ) VALUES ( 
6, 3, 'CNT4'); 
INSERT INTO EMP_DETAILS ( ID, EMP_ID, COUNTRY_NO ) VALUES ( 
7, 4, 'CNT5'); 
INSERT INTO EMP_DETAILS ( ID, EMP_ID, COUNTRY_NO ) VALUES ( 
8, 5, 'CNT6'); 
INSERT INTO EMP_DETAILS ( ID, EMP_ID, COUNTRY_NO ) VALUES ( 
9, 5, 'CNT5'); 

INSERT INTO DOCUMENT ( ID, NAME ) VALUES ( 
1, 'AAAA'); 
INSERT INTO DOCUMENT ( ID, NAME ) VALUES ( 
2, 'BBBB'); 
INSERT INTO DOCUMENT ( ID, NAME ) VALUES ( 
3, 'CCCC'); 
INSERT INTO DOCUMENT ( ID, NAME ) VALUES ( 
4, 'DDDD'); 
commit;


Final Result
************

ID	NAME	REF_ID_1
4	STEWART 3	
5       HUGHES  4 

Re: how to compare data with other child data (merged) [message #385316 is a reply to message #385308] Mon, 09 February 2009 01:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
OK, sorry, I forgot to precise... and explain each line of your result. Why these 2 lines?

Regards
Michel
Re: how to compare data with other child data (merged) [message #385323 is a reply to message #385316] Mon, 09 February 2009 01:51 Go to previous messageGo to next message
chaituu
Messages: 115
Registered: June 2008
Senior Member
see employee-1 has one line in EMP_DETAILS(1 emp can have many lines in EMP_DETAILS but for employee-1 has only one line) which is having COUNTRY_NO-CNT1;same COUNTRY_NO(CNT1) is there for employee-2 &3 also but for employee-3 document is not created(REF_ID_1 is null).

see employee-4 which is having COUNTRY_NO-CNT5;same COUNTRY_NO(CNT5) is there for employee-5 also and for both employees(4&5) document is created(i.e REF_ID_1 is not null).
Re: how to compare data with other child data (merged) [message #385332 is a reply to message #385323] Mon, 09 February 2009 02:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> with 
  2    selected_country as (
  3      select d.country_no
  4      from emp e, emp_details d
  5      where d.emp_id = e.id
  6      group by d.country_no
  7      having count(*) = count(e.ref_id_1)
  8    )
  9  select * 
 10  from emp e, emp_details d
 11  where d.emp_id = e.id
 12    and d.country_no in (select country_no from selected_country)
 13  order by e.id, d.country_no
 14  /
        ID NAME              REF_ID_1         ID     EMP_ID COUNTRY_NO
---------- --------------- ---------- ---------- ---------- ---------------
         2 SCOTT                    2          3          2 CNT2
         4 STEWART                  3          7          4 CNT5
         5 HUGHES                   4          9          5 CNT5
         5 HUGHES                   4          8          5 CNT6

4 rows selected.

If you want only groups with at least 2 members (which is not in your requirements but in your result:
SQL> with 
  2    selected_country as (
  3      select d.country_no
  4      from emp e, emp_details d
  5      where d.emp_id = e.id
  6      group by d.country_no
  7      having count(*) = count(e.ref_id_1) and count(*) > 1
  8    )
  9  select * 
 10  from emp e, emp_details d
 11  where d.emp_id = e.id
 12    and d.country_no in (select country_no from selected_country)
 13  order by e.id, d.country_no
 14  /
        ID NAME              REF_ID_1         ID     EMP_ID COUNTRY_NO
---------- --------------- ---------- ---------- ---------- ---------------
         4 STEWART                  3          7          4 CNT5
         5 HUGHES                   4          9          5 CNT5

2 rows selected.

Regards
Michel

[Updated on: Mon, 09 February 2009 02:42]

Report message to a moderator

Re: how to compare data with other child data (merged) [message #385366 is a reply to message #385332] Mon, 09 February 2009 05:11 Go to previous messageGo to next message
chaituu
Messages: 115
Registered: June 2008
Senior Member
Michel,

wrong results will come with the below query.

in the 1st query you have written, actually scott should not come in the final result because SCOTT having country_no 'CNT1' apart from 'CNT2'. the country_no 'CNT1' has alo been assigned to employee 'MIKE' which is having REF_ID_1 is null so in the final result SCOTT should not come.

if we take 2nd query also if i add below rows then 2nd query will not fetch below record.

INSERT INTO EMP ( ID, NAME, REF_ID_1 ) VALUES (
6, 'SHYAM', 6);
INSERT INTO EMP_DETAILS ( ID, EMP_ID, COUNTRY_NO ) VALUES (
10, 6, 'CNT7');

Re: how to compare data with other child data (merged) [message #385373 is a reply to message #385366] Mon, 09 February 2009 05:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
in the 1st query you have written, actually scott should not come in the final result because SCOTT having country_no 'CNT1' apart from 'CNT2'.

This is why clear specifications are mandatory. You didn't mention that ALL countries of employee should be in the "selected countries".

What should be the result with your new rows?
Speaking as set theory, what are the requirements?
You want the rows of emp that satify... (complete the sentence)
For instance, all the countries of the rows in the result must contain only rows that are in the result set. Is this true?

Regards
Michel

Re: how to compare data with other child data (merged) [message #385380 is a reply to message #385366] Mon, 09 February 2009 06:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
With your remark, the statement becomes (as far as I understand the requirements and with details about countries):
SQL> with 
  2    selected_country as (
  3      select d.country_no, count(*), count(e.ref_id_1)
  4      from emp e, emp_details d
  5      where d.emp_id = e.id
  6      group by d.country_no
  7      having count(*) = count(e.ref_id_1)
  8    )
  9  select * 
 10  from emp e, emp_details d
 11  where d.emp_id = e.id
 12    and d.country_no in (select country_no from selected_country)
 13    and not exists (
 14          select null from emp_details
 15          where emp_id = e.id
 16            and country_no not in (select country_no from selected_country))
 17  order by e.id, d.country_no
 18  /
        ID NAME              REF_ID_1         ID     EMP_ID COUNTRY_NO
---------- --------------- ---------- ---------- ---------- ---------------
         4 STEWART                  3          7          4 CNT5
         5 HUGHES                   4          9          5 CNT5
         5 HUGHES                   4          8          5 CNT6
         6 SHYAM                    6         10          6 CNT7

4 rows selected.

Regards
Michel
Re: how to compare data with other child data (merged) [message #385382 is a reply to message #385380] Mon, 09 February 2009 06:09 Go to previous messageGo to next message
chaituu
Messages: 115
Registered: June 2008
Senior Member
we can remove extra subqueries.see this

WITH 
      SELECTED_COUNTRY AS (
        SELECT D.COUNTRY_NO, COUNT(*),COUNT(E.REF_ID_1)
        FROM EMP E, EMP_DETAILS D
        WHERE D.EMP_ID = E.ID  
        GROUP BY D.COUNTRY_NO
        HAVING COUNT(*) = COUNT(E.REF_ID_1)  
      )
    SELECT DISTINCT *
   FROM EMP E
   WHERE NOT EXISTS (
           SELECT 'x' FROM EMP_DETAILS
           WHERE EMP_ID = E.ID
             AND COUNTRY_NO NOT IN (SELECT COUNTRY_NO FROM SELECTED_COUNTRY))
       ORDER BY E.ID;
Re: how to compare data with other child data (merged) [message #385388 is a reply to message #385294] Mon, 09 February 2009 06:19 Go to previous messageGo to next message
chaituu
Messages: 115
Registered: June 2008
Senior Member
no need to write distinct.
Re: how to compare data with other child data (merged) [message #385392 is a reply to message #385382] Mon, 09 February 2009 06:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You're right I added the new condition without seeing that it implies the previous one.

Regards
Michel
Re: how to compare data with other child data (merged) [message #385410 is a reply to message #385294] Mon, 09 February 2009 07:54 Go to previous messageGo to next message
chaituu
Messages: 115
Registered: June 2008
Senior Member
thanks for the support
Re: how to compare data with other child data (merged) [message #385490 is a reply to message #385294] Mon, 09 February 2009 22:35 Go to previous messageGo to next message
chaituu
Messages: 115
Registered: June 2008
Senior Member
I have rewritten above query like this;

SELECT *
FROM EMP E
WHERE NOT EXISTS (SELECT 'X'
FROM EMP_DETAILS ED, EMP_DETAILS ED1 , EMP E1
WHERE E.ID = ED.EMP_ID
AND ED.COUNTRY_NO = ED1.COUNTRY_NO
AND E1.ID=ED1.EMP_ID
AND E1.REF_ID_1 IS NULL);
Re: how to compare data with other child data (merged) [message #385491 is a reply to message #385294] Mon, 09 February 2009 22:46 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
which looks better your post above or mine below:
SELECT * 
FROM   emp e 
WHERE  NOT EXISTS (SELECT 'X' 
                   FROM   emp_details ed, 
                          emp_details ed1, 
                          emp e1 
                   WHERE  e.id = ed.emp_id 
                          AND ed.country_no = ed1.country_no 
                          AND e1.id = ed1.emp_id 
                          AND e1.ref_id_1 IS NULL);


You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you

[Updated on: Mon, 09 February 2009 22:48]

Report message to a moderator

Re: how to compare data with other child data (merged) [message #385501 is a reply to message #385491] Mon, 09 February 2009 23:12 Go to previous messageGo to next message
chaituu
Messages: 115
Registered: June 2008
Senior Member
Both are same naa.
Re: how to compare data with other child data (merged) [message #385570 is a reply to message #385501] Tue, 10 February 2009 02:46 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
They are the same query, but as @BlackSwan points out, one of them (the one he posted) is much easier to read.
Previous Topic: CSV file
Next Topic: Error while using ANYDATA through VARRAY
Goto Forum:
  


Current Time: Sun Dec 11 04:10:17 CST 2016

Total time taken to generate the page: 0.07222 seconds