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 :
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!