Databases
StackOverflow2013
Postsdbo
POSingle out duplicates between two result sets
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSingle out duplicates between two result sets
    primarykey
    data
    text
    <p>I'm having trouble with a query I'm trying to do. I'm trying to select values that come up in to result sets.</p> <p>I'm currently dealing with two queries:</p> <p>A)</p> <pre>SELECT /*+ RULE */ pi.compressed_name, pi.phn, to_char(pi.date_of_birth , 'YYYY/MM/DD') as date_of_birth, gen.description as gender , to_char(pe.started_on , 'YYYY/MM/DD' ) as medicare_eligibility_start , to_char(pe.ended_on , 'YYYY/MM/DD' ) as medicare_eligibility_end FROM medcrtr.forest_node fnpppp, medcrtr.forest_node fnppp, medcrtr.forest_node fnpp, medcrtr.forest_node fnp, medcrtr.forest_node fn, medcrtr.group_member gm, medcrtr.group_type gt, medcrtr.program_eligibility pe, person_index pi, gender_type gen WHERE gm.entity_type_id = 1 --:P_PERSON_ENTITY_TYPE_ID AND gen.id = pi.gender_code AND gt.id = gm.group_id AND gt.category_id = 1 --icgroupmemebrcategory :P_GROUP_CATEGORY_ID AND fn.source_id = group_id AND fn.entity_type_id = 3 --icGOM:P_ENTITY_TYPE_ID AND fnp.id = fn.parent_id AND fnpp.id = fnp.parent_id AND fnppp.id = fnpp.parent_id AND fnpppp.id = fnppp.parent_id AND pe.person_id = gm.source_id AND pe.sub_program_id = fnpp.parent_id AND pi.person_id = gm.source_id AND fnppp.id = 1169 AND (gm.ended_on >= SYSDATE OR gm.ended_on IS NULL)</pre> <p>B) Same as above other than second last line, in which</p> <pre>AND fnppp.id = 1715</pre> <p>So now the first query returns 1536 records, and the second returns 2067. There are however records(people) that come up in both queries. What I want to do is single them out. (Essentially I want to do an intersection so I"ll look into that)</p> <p>So far I've tried to do the following:</p> <p>-I've added the following to query A: <pre>AND pi.person_id NOT IN (SELECT /*+ RULE */ pi.person_id FROM ... )</pre> where the query inside the brackets is query B (other than for the first line). This returns about 10 rows (I'm not sure that's accurate).</p> <p>-I then ran the same query except with <code>NOT IN</code>. That gives me a result of about 200(not sure if this is accurate).</p> <p>Now those two numbers obviously don't add up to 1536, which I think should happen? So obviously one or both of them is wrong.</p> <p>Can anyone tell what I'm doing wrong? Thanks for your help.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USThe_Denominater
    1. USThe_Denominater
    plurals
    1. PL
      singulars
      1. LTLinked
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COLooks like you've got hierarchical data--referring to all the `medcrtr.forest_node` references--but you're not using the Oracle hierarchical syntax `CONNECT BY`...
      singulars
      1. POSingle out duplicates between two result sets
      1. USOMG Ponies
    2. COCould you clarify what you mean by "single them out"? Do you want to only see the rows that match & are attributed to both the `fnppp.id` values (use `INTERSECT`), or only see unique rows (use `UNION`)
      singulars
      1. POSingle out duplicates between two result sets
      1. USOMG Ponies
    3. COThanks for the response. INTERSECT is what I should have been using all along. I'm trying to implement the CONNECT BY syntax though, but I'm not sure how to start, do you have any tips on where to start? Also, what are the advantages of it as opposed to the way my queries are written, other than cleanness?
      singulars
      1. POSingle out duplicates between two result sets
      1. USThe_Denominater
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload