Query for Multiple Patient Field Defs

For complex topics that regular users would not be interested in. For power users and database administrators.
Post Reply
efgrp
Posts: 4
Joined: Tue Feb 20, 2018 6:09 pm

Query for Multiple Patient Field Defs

Post by efgrp » Tue Feb 20, 2018 6:20 pm

Query Help Needed!
I am trying to write a query to return a "table" with the field names and field values for multiple patient field definitions. Is this possible? Every query example from the Open Dental website that returns patient definition fields only returns one field and one value. The goal of this query is to return the Patient Name for patients that have a field value for PatFieldName1 and also show the field value for the PatFieldName2. Here is what I have so far:

SET @PatFieldName1='H&P Signed by MD/PO/PA';
SET @PatFieldName2 = 'H&P Received';
SET @pos=0;
SELECT @pos:=@pos+1 AS 'Count', A.*
FROM (
SELECT CONCAT(p.LName,', ',p.FName) AS 'Patient Name',
pf.FieldName,
pf.FieldValue
FROM patient p
INNER JOIN patfield pf ON pf.PatNum=p.PatNum
AND pf.FieldName LIKE @PatFieldName1
ORDER BY p.LName, p.FName
) A

User avatar
cmcgehee
Posts: 711
Joined: Tue Aug 25, 2015 5:06 pm
Location: Salem, Oregon

Re: Query for Multiple Patient Field Defs

Post by cmcgehee » Wed Feb 21, 2018 8:30 am

I added a few OR clauses to the JOIN and that should do the trick. If you don't need the 3rd and 4th pat field, you can leave them blank.


SET @PatFieldName1='H&P Signed by MD/PO/PA';
SET @PatFieldName2 = 'H&P Received';
SET @PatFieldName3 = '3rd Pat Field';
SET @PatFieldName4 = '4th Pat Field';
SET @pos=0;
SELECT @pos:=@pos+1 AS 'Count', A.*
FROM (
SELECT CONCAT(p.LName,', ',p.FName) AS 'Patient Name',
pf.FieldName,
pf.FieldValue
FROM patient p
INNER JOIN patfield pf ON pf.PatNum=p.PatNum
AND (pf.FieldName LIKE @PatFieldName1 OR pf.FieldName LIKE @PatFieldName2 OR pf.FieldName LIKE @PatFieldName3 OR pf.FieldName LIKE @PatFieldName4)
ORDER BY p.LName, p.FName
) A
Chris McGehee
Open Dental Software
http://www.opendental.com

tgriswold
Posts: 122
Joined: Fri Jun 07, 2013 8:52 am

Re: Query for Multiple Patient Field Defs

Post by tgriswold » Wed Feb 21, 2018 11:48 am

Might be useful to also change ORDER BY to "ORDER BY p.LName, p.FName, pf.FieldName" all patients will show the fields in the same order. Also, I think the joins might need to be left joins instead of inner joins (if you want to show patients even if they dont have a patfield filled out), because I don't think patfields exist unless they've been set.
Also, if you want one row to show patient name and multiple patient fields on the same row you would need separate joins, and one entry for each join in the select, or a group by patient with a complicated group_concat with a if/case statement.
Travis Griswold
Open Dental Software
http://www.opendental.com

efgrp
Posts: 4
Joined: Tue Feb 20, 2018 6:09 pm

Re: Query for Multiple Patient Field Defs

Post by efgrp » Thu Feb 22, 2018 7:05 am

Thank you for your help!!!

Post Reply