• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 239
  • Last Modified:

Finding duplicate records on the same date

I have a site that tests children for autism and keeps a record of each test.

Clients are doctors, each with unique ID ('license_id')

I'm trying to write a query that will find a specific Dr's records for the children who were tested more than once on the same date.

data collected is posted to two tables: 'subjects' AND 'screen'
'subjects' = child_fname,child_lname,subject_id(auto-increment),license_id

'screen' = test_results,subject_id, test_date

'subject_id' is the common field between the two tables
 
BUT - - records will NOT have the same 'subject_id'  - so 'WHERE subjects.subject_id = screen.subject_id'  will not work to join the two tables.

In plain language, the query needs to :

SELECT subjects.subject_fname,subjects.subject_lname,screen.test_date
FROM subjects, screen
WHERE subjects.license_id = '516'
AND screen.test_date BETWEEN '$date1' and '$date2'
GROUP BY ???

example of desired result:
NAME  | SUBJECT_ID | TEST_DATE
------------------------------------------
Jones, Bob | 11111 | 2011-04-10
Jones, Bob | 22222 | 2011-04-10
Smith, Sue  | 33333 | 2011-04-12
Smith, Sue  | 44444 | 2011-04-12

It would be easier if the "duplicates" were "copies" of the same record (subject_id was the same), but since each is an individual test, but given on the same day, I can't seem to find any way to create a list of them...

Any and all help and comments appreciated. You guys are great!
0
michlcamp
Asked:
michlcamp
  • 5
  • 4
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this should do, if I understood the requirements correctly:
SELECT subjects.subject_fname, group_concat(subjects.subject_lname),screen.test_date 
FROM subjects, screen 
WHERE subjects.license_id = '516' 
AND screen.test_date BETWEEN '$date1' and '$date2'
GROUP BY subjects.subject_fname, screen.test_date 
HAVING COUNT(*) > 1

Open in new window

0
 
michlcampAuthor Commented:
Nope, that didnt' work well at all....(sorry) - maybe it's in my WHILE loop - but it produced this:

Smith,Smith, Bob -
Smith,Smith,Smith,Smith,Smith,Smith,Smith,Smith,Smith,Smith,Smith,Smith,Smith,Smith,Smith,Smith,Smith,
etc....
while ($row = mysql_fetch_array($result)) {    
    echo "$row[1],  $row[0] - $row[2]<br>";
   }

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
ok, I misunderstood ...
SELECT subjects.subject_fname, subjects.subject_lname, group_concat(subjects.subject_id),screen.test_date 
FROM subjects, screen 
WHERE subjects.license_id = '516' 
AND screen.test_date BETWEEN '$date1' and '$date2'
GROUP BY subjects.subject_fname, subjects.subject_lname, screen.test_date 
HAVING COUNT(*) > 1

Open in new window

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
but there seems to be a missing JOIN condition for the 2 tables subjects and screen, no?
0
 
michlcampAuthor Commented:
yes, usually I join them on 'subject_id', but since the records I'm looking for won't have the same subject_id, can I still join them on that field?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
sure, you even have to.
0
 
minjosefaCommented:
select sub.child_fname
, sub.child_lname
, sub.subject_id
, scr.test_date
from subjects sub
, screen scr,
(
  select sub.child_fname
  , sub.child_lname
  , scr.test_date 
  from subjects sub
  , screen scr
  where scr.subject_id = sub.subject_id
  group by sub.child_fname
  , sub.child_lname
  , scr.test_date
  having count(scr.test_date) >= 2
) sub_scr
where scr.subject_id = sub.subject_id
and sub.child_fname = sub_scr.child_fname
and sub.child_lname = sub_scr.child_lname
and scr.test_date = sub_scr.test_date
order by sub.child_fname
, sub.child_lname
, scr.test_date

Open in new window

0
 
michlcampAuthor Commented:
Never seen that one before...thanks, but that didn't work...0 records returned - and doesn't reference license_id so no records are associated with any particular client...

I'm  trying to create a list of records for a specific client (license_id) with the same first and last name (from table 'subjects') on the same test_date (in table 'screen').

Looking for children tested twice or more on the same day. Those are the ones I have to count and display.
0
 
michlcampAuthor Commented:
I made a few adjustments and this code did exactly what I was hoping to do. Wow...more complicated than I'm used to. Thanks a million!
0
 
michlcampAuthor Commented:
This is the code I ended up with. Displays all duplicates. From here I can extract the individual names as the originals and will be left with duplicates only. Many thanks! Sorry to jump to conclusions when I first tried your code - once I looked it over I experimented a bit and got it to work the way I'd described.
Thanks again.
$query5 = "SELECT sub.subject_fname, 
                 sub.subject_lname, 
                 sub.subject_id, 
                 scr.test_date 
                 FROM subjects sub, screen scr,(SELECT sub.subject_fname,                                             sub.subject_lname, sub.subject_id,                                                    scr.test_date 
FROM subjects sub, screen scr WHERE scr.subject_id = sub.subject_id AND sub.license_id = '$lid' AND scr.test_date BETWEEN '$date1' and '$date2' GROUP BY sub.subject_fname, sub.subject_lname, scr.test_date                                                HAVING COUNT(scr.test_date) >= 2) sub_scr WHERE scr.subject_id = sub.subject_id AND sub.subject_fname = sub_scr.subject_fname AND sub.license_id = '$lid' AND sub.subject_lname = sub_scr.subject_lname  AND scr.test_date = sub_scr.test_date  AND scr.test_date BETWEEN '$date1' and '$date2' ORDER BY UPPER(sub.subject_lname), sub.subject_fname, scr.test_date";

Open in new window

0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now