Hi,

I was not successful in writing a statement for this task:

StatusTable (the status of a patient is stored)

PatientID: integer;  // any integer-value
Status: integer;     // 0 or 1
Private: boolean;

Example:

32124,0,false;
32124,1,true;
32125,0,false;
32126,0,false;

A Patient can have 1 or 2 entries here with status = 0 or status = 1 or
both.

What I want to know by running a query against this table is:

Show me all records where the same patient has both status 0 and 1 and
where ((status = 0 and private = false) and (status = 1 and private = true))

The reason is, that I have to correct the table if there is a record
with the same PatientID and (status = 0, private = false) AND (status =
1, private = true);

IOW: If (status = 1 and private = true) then status with value 0 has to
be private too.

I hope I could make that clear.

Can that be done in one query?

Thanks and regards Uli

Uli


Not a finished product but

select
patientid,
private,
sum(status),
sum(IF(private,1,0)+status)as both,
sum(IF(private,1,0)) as single
from uli
where patientid in (select PatientID from Uli
group by PatientID
having   count(PatientID) > 1)
group by patientid

In my quick tests if the patient is clean then both returns 3, single returns 1, if incorrect then both returns 2, single returns 1

Roy Lambert [Team Elevate]

Roy,

> select
> patientid,
> private,
> sum(status),
> sum(IF(private,1,0)+status)as both,
> sum(IF(private,1,0)) as single
> from uli
> where patientid in (select PatientID from Uli
> group by PatientID
> having   count(PatientID) > 1)
> group by patientid
>
> In my quick tests if the patient is clean then both returns 3, single returns 1, if incorrect then both returns 2, single returns 1

Not bad. Good idea to accumulate status and private.
Just having a deep look into it, in order to understand what's happening
there.

Uli

Roy,

that works. Thank you.
Here the final statement:

select patientenid,
sum(IF(privat,1,0)+status)as both,
sum(IF(privat,1,0)) as single
from scheine
where patientenid in (select patientenid from Scheine
group by patientenid
having  count(patientenid) = 2)
group by patientenid
having both = 3 and single = 1

Regards Uli

Uli


I thought you wanted the invalid ones, doesn't that give you the valid ones or am I getting confused.

Roy Lambert

Roy,

> I thought you wanted the invalid ones, doesn't that give you the valid ones or am I getting confused.

You are not.

Just a typo, I realized it when executing the query.

Uli