Databases
StackOverflow2013
Postsdbo
POMS Access Pass Through Query find duplicates using multiple tables
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMS Access Pass Through Query find duplicates using multiple tables
    text
    copied!<p>I'm trying to find all coverage_set_id with more than one benefit_id attached summary_attribute (value=2004687). The query seems to be working fine without the GROUP BY &amp; HAVING parts, but once I add those lines in (for the COUNT) my results are incorrect. Just trying to get duplicate coverage_set_id.</p> <p>Pass-Through Query via OBDC database:</p> <pre><code>SELECT DISTINCT b.coverage_set_id, COUNT (b.coverage_set_id) AS "COUNT" FROM coverage_set_detail_view a JOIN contracts_by_sub_group_view b ON b.coverage_set_id = a.coverage_set_id JOIN request c ON c.request_id = b.request_id WHERE b.valid_from_date BETWEEN to_date('10/01/2010','mm/dd/yyyy') AND to_date('12/01/2010','mm/dd/yyyy') AND c.request_status = 1463 AND summary_attribute = 2004687 AND benefit_id &lt;&gt; 1092333 GROUP BY b.coverage_set_id HAVING COUNT (b.coverage_set_id) &gt; 1 </code></pre> <p>My results look like this:</p> <pre><code>----------------------- COVERAGE_SET_ID | COUNT ----------------------- 4193706 | 8 4197052 | 8 4193926 | 112 4197078 | 96 4174168 | 8 </code></pre> <p>I'm expecting all the COUNTs to be 2. Thank you.</p> <hr> <h2>::EDIT::</h2> <p>Solution:</p> <pre><code>SELECT c.coverage_set_id AS "COVERAGE SET ID", c1.description AS "Summary Attribute", count(d.benefit_id) AS "COUNT" FROM ( SELECT DISTINCT coverage_set_id FROM contracts_by_sub_group_view WHERE valid_from_date BETWEEN '01-OCT-2010' AND '01-DEC-2010' AND request_id IN ( SELECT request_id FROM request WHERE request_status = 1463) ) a JOIN coverage_set_master e ON e.coverage_set_id = a.coverage_set_id JOIN coverage_set_detail c ON c.coverage_set_id = a.coverage_set_id JOIN benefit_summary d ON d.benefit_id = c.benefit_id AND d.coverage_type = e.coverage_type JOIN codes c1 ON c1.code_id = d.summary_attribute WHERE d.summary_attribute IN (2004687, 2004688) AND summary_structure = 1000217 GROUP BY c.coverage_set_id, c1.description HAVING COUNT(d.benefit_id) &gt; 1 ORDER BY c.coverage_set_id, c1.description </code></pre> <p>And these were the results:</p> <pre><code>COVERAGE SET ID | SUMMARY ATTRIBUTE | COUNT ------------------------------------------------- 4174168 | INPATIENT | 2 4174172 | INPATIENT | 2 4191828 | INPATIENT | 2 4191832 | INPATIENT | 2 4191833 | INPATIENT | 2 4191834 | INPATIENT | 2 4191838 | INPATIENT | 2 4191842 | INPATIENT | 2 4191843 | INPATIENT | 2 4191843 | OUTPATIENT | 2 4191844 | INPATIENT | 2 4191844 | OUTPATIENT | 2 </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

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