Open Dental Forum

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

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

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.

Thank you for your help!!!