CODEWITHSUNDEEP
mysqlsql
shellp
shellp

Reputation: 1

Distinct Count from a Many Table in a One-To-Many Relationship

I am a newbie to SQL so I apologize if this query is basic.

I have a patient data table that has singularly occurring fields i.e. chart_number, Name, with the unique identifier of zzabstractlink. I have another table of procedures and there could be up to 20 per patient with the data in long format.

I am also linking to another multi occurring table, diagnoses, so should I be creating 3 different datasets with all the fields and derived fields (like above for c-sections) and then link them together via a union statement? Or will whatever the solution for the procedures work with the diagnoses?

All assistance greatly appreciated, thanks.

note: for the code provided, this pulled over a case from the intervention table for as many records as there were interventions and not just once, which is what I would like.

S.

patient table

zzabstractlink name patient_service admit_date disch_date
111111 Smith, John 30 2021-09-01 2021-09-05
222222 Jones, Frank 30 2021-10-01 2021-10-15
333333 Green, Joni 55 2021-11-01 2021-11-10
444444 Black, Mitzi 51 2021-12-01 2021-12-03
555555 Cameron, Sally 10 2022-01-01 2022-05-05

procedure table

zzabstractlink PxOccur PxCode
111111 1 INV89DA
111111 2 ITA35HAJ2
111111 3 ITA80DA
222222 1 IAN27JA
222222 2 1BM72LA
222222 3 1FR89WJAK
333333 1 1RM89LA
333333 2 1RF89LA
444444 1 5MD60AA

diagnosis table

zzabstractlink DxOccur DxCode
111111 1 K711
111111 2 R458
222222 1 K358
222222 2 I651
333333 1 N811
333333 2 N816
444444 1 O70101
444444 2 Z3700
555555 1 I500
555555 2 E785
555555 3 R64

[desired outcome]

zzabstractlink cs(derived field from px) prev_cs(derived field from dx)
111111 0 0
222222 0 0
333333 0 0
444444 1 1
555555 0 0

Upvotes: 0

Views: 104

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

I don't understand how you get to the prev_cs value. But as to the cs value, you want an EXISTS or IN clause:

select
  zzabstractlink,
  zzabstractlink in (select zzabstractlink from procedures where pxcode like '5MD60%')
    as cs
from patients
order by zzabstractlink;

In MySQL true = 1, false = 0. The IN or EXISTS clause will hence be 1 if you find a matching row and 0 otherwise.

Demo: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=00501fd3d98f63a35ad2c4cb527c7f8e

Upvotes: 0

Related Questions