How do I "Find" the Missing Rows in this Self-Join?

SQL
1

Greetings - I am trying to pull multiple sets of dates and test results for 20 individuals from a database I have created, returning one row for each distinct (fictional) patient. Eighteen of the individuals have 2 test dates and corresponding results. 2 individuals only have one test date and result. I programmed a self-join to create the view (based on the database I have created), which returns a row for the 18 individuals who have two sets of dates/test values. The other two patients do not show up in the result set. I have tried using a left join, but that did not change anything. Thoughts? I’m new to MySQL and coding in general, and I am confident there is a way to do this, but I haven’t been able to figure it out. Thanks in advance for your help! I’ve copied and pasted my self-join statement here:

SELECT
DISTINCT v1.patient_id,
pr.last_name AS primary_provider,
p.last_name,
p.preferred_name,
TIMESTAMPDIFF(YEAR,p.date_of_birth, CURDATE()) AS age,
p.sex,
v1.date,
v1.a1c_value,
v2.date,
v2.a1c_value,
v2.a1c_value - v1.a1c_value AS change_over_time
FROM providers pr
JOIN patients p
ON p.provider_id = pr.provider_id
JOIN a1c_values v1
ON v1.patient_id = p.patient_id
JOIN a1c_values v2
ON v1.patient_id = v2.patient_id
AND v1.test_id < v2.test_id
ORDER BY v1.patient_id;

Again, thanks in advance for your help!

2

I am relearning SQL a bit. But, I think you may have been on the right track with the left join. From reading the code, I am guessing the problem is needing a LEFT JOIN with the a1c_values v2. Because if the patient did not meet this condition, it would not be listed in the results.

Hope this helps.

1 Like
3

Hello, and thank you for responding. I appreciate your suggestion. I had thought of the LEFT JOIN, too, but for whatever reason, that also failed to return the two “missing” rows, and I still am not certain why. I ended up adding a UNION statement to self-join a third time – that did the trick. I doubt that is the best solution to the problem, but at least it worked. :slightly_smiling_face: