Right Outer Join? 1

[Crystalguru]

I am using MS SQL Server 2000

Here's my current data:

Patient Name Bed Num
Doe, Jon 1101
Smith, Mary 1102
Carls, Don 1105

What I would like to see is this:

Patient Name Bed Num
Null 1100
Doe, Jon 1101
Smith, Mary 1102
Null 1103
Null 1104
Carls, Don 1105

The two columns are coming from:
Patient and Location table.

I was working towards something like this:
select patient_name, bed_num
from patient
right outer join location
on patient.locationguid = location.guid

Still not getting it. Any ideas?

 

[ethorn10]

select patient_name, 
     bed_num
from location left outer join
     patient on location.guid = patient.locationguid
where patient.locationguid is null

That should do the trick.

 

[vbkris]

>>Still not getting it. Any ideas?

wht data are u getting???

Known is handfull, Unknown is worldfull

 

[Crystalguru]

Here's the actual query I am using:
SELECT
CV3Location.Name,
CV3location.typecode,
Unit.GUID as LocationGroupGUID,
Unit.Name as Unit,
CV3ClientVisit.ClientDisplayName,
cv3clientvisit.internalvisitstatus
FROM
CV3Location
JOIN CV3Location Unit
ON CV3Location.LocnGrpGUID=Unit.GUID
AND Unit.Active = 1
LEFT OUTER JOIN CV3ClientVisitLocation

ON CV3Location.GUID = CV3ClientVisitLocation.LocationGUID
left outer join (select guid, clientdisplayname, internalvisitstatus
from cv3clientvisit
where cv3clientvisit.internalvisitstatus ='ADM'
) as cv3clientvisit
ON CV3ClientVisitLocation.ClientVisitGUID = CV3ClientVisit.GUID
WHERE Unit.name like 'AGH-11%'
ORDER BY
CV3Location.Name ASC

I only want the ADM patients (currently admitted). What I keep getting is rows of patients that "were" in the beds. Notice the NULL NULL. These patients are now Discharged or Closed.

Here's the data:
AGH-11C-1161-01 Bed AGH-11C SCMLEWIS, INPTONE ADM
AGH-11C-1161-01 Bed AGH-11C NULL NULL
AGH-11C-1161-02 Bed AGH-11C SMCSTILL, ONE ADM
AGH-11C-1161-02 Bed AGH-11C NULL NULL

Do you notice that there is a repeat - 1161-01, 02? That was a patient but now they are DSC or CLS.

Here's what I would like to see:
AGH-11C-1161-01 Bed AGH-11C SCMLEWIS, INPTONE ADM
REMOVE----AGH-11C-1161-01 Bed AGH-11C NULL
AGH-11C-1161-02 Bed AGH-11C SMCSTILL, ONE ADM
REMOVE----AGH-11C-1161-02 Bed AGH-11C NULL
AGH-11C-1162-01 Bed AGH-11C Null
AGH-11C-1162-02 Bed AGH-11C DOE, JOHN ADM

Can I get rid of the duplicate beds? And only get the beds where there is a currently admitted patient and the beds that are currently empty?

Thanks.

 

[gmmastros]

Let's build this in stages.

First, does this return all beds?

Select CV3Location.Name, 
       CV3location.typecode
From   CV3location

Second, does this return all beds that currently have a patient?

Select CV3ClientVisitLocation.*
From   CV3ClientVisitLocation
       Inner Join CV3ClientVisit
         On CV3ClientVisit.GUID = CV3ClientVisitLocation.ClientVisitGUID
Where  CV3ClientVisit.internalvisitstatus = 'ADM'

If either of these queries isn't right, then post back with the corrected version. From there, we should be able to build a query that does what you need it to.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Crystalguru]

This query:

Select CV3Location.Name, 
       CV3location.typecode
From   CV3location

Returns: TypeCode =Units, Rooms, Beds

To get just beds:

Select CV3Location.Name, 
       CV3location.typecode
From   CV3location
WHERE CV3Location.TypeCode = 'Bed'

This query:

Select CV3ClientVisitLocation.*
From   CV3ClientVisitLocation
       Inner Join CV3ClientVisit
         On CV3ClientVisit.GUID = CV3ClientVisitLocation.ClientVisitGUID
Where  CV3ClientVisit.internalvisitstatus = 'ADM'

Returns: All admitted patients and all the locations they could have been transferred to.

To get only current permanent location (bed), which we call the patient's current location, I would use the following:

Select CV3ClientVisitLocation.clientlocntypecode, status,
cv3ClientVisit.currentlocation
From   CV3ClientVisitLocation
       Inner Join CV3ClientVisit 
         On CV3ClientVisit.GUID = CV3ClientVisitLocation.ClientVisitGUID
Where  CV3ClientVisit.internalvisitstatus = 'ADM'
and CV3ClientVisitLocation.ClientLocnTypeCode = 'P'
and CV3ClientVisitLocation.Status = 'CUR'

Not a fun database to work with...it keeps the mind ticking..

 

[gmmastros]

I can see that your database is, uh, interesting.

Taking your 2 queries and combining them...

Select AllBeds.Name,
       OccupiedBeds.Status
From   (
       [blue]Select CV3Location.Name, 
              CV3location.typecode
       From   CV3location
       WHERE  CV3Location.TypeCode = 'Bed'[/blue]
       ) As AllBeds
       Left Join (
           [purple]Select CV3ClientVisitLocation.clientlocntypecode, 
                  status,
                  cv3ClientVisit.currentlocation
           From   CV3ClientVisitLocation
                  Inner Join CV3ClientVisit 
                    On CV3ClientVisit.GUID = CV3ClientVisitLocation.ClientVisitGUID
           Where  CV3ClientVisit.internalvisitstatus = 'ADM'
                  and CV3ClientVisitLocation.ClientLocnTypeCode = 'P'
                  and CV3ClientVisitLocation.Status = 'CUR'[/purple]
           ) As OccupiedBeds
           On AllBeds.SomeField = OccupiedBeds.SomeField

Notice the join On AllBeds.SomeField = OccupiedBeds.SomeField You'll have to find the field in common to link the 2 derived tables. Ideally, this would be the unique identifier for each bed. Whatever this unique identifier is, you will need to include it in both derived tables (the blue query and the purple query).

Does this help?


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Crystalguru]

Shazam! We may have it!

Select AllBeds.Name,
       OccupiedBeds.Status,
	OccupiedBeds.ClientdisplayName
From   (
       Select CV3Location.Name, 
              CV3location.typecode,
		CV3location.guid
       From   CV3location
       WHERE  CV3Location.TypeCode = 'Bed'
       ) As AllBeds
       Left Join (
           Select CV3ClientVisitLocation.clientlocntypecode,
CV3Location.Name,CV3Location.GUID,CV3Location.status,
                  cv3ClientVisit.currentlocation,
		cv3clientvisit.currentlocationguid,
		CV3clientvisit.clientdisplayname
           From   CV3ClientVisitLocation
                  Inner Join CV3ClientVisit 
                    On CV3ClientVisit.GUID = CV3ClientVisitLocation.ClientVisitGUID
		Inner Join CV3Location 
			On CV3ClientVisitlocation.LocationGUID = CV3Location.GUID
           Where  CV3ClientVisit.internalvisitstatus = 'ADM'
                  and CV3ClientVisitLocation.ClientLocnTypeCode = 'P'
                  and CV3ClientVisitLocation.Status = 'CUR'
           ) As OccupiedBeds
           On AllBeds.GUID = OccupiedBeds.guid
where allbeds.name like 'AGH-11c%'
order by allbeds.name

There's still some work to do and some testing. But the bulk of it is here!
Thank you so much for taking the time out of your day to help me. It's people like you that make us look good to our clients!!

Thanks again.

 

[gmmastros]

You are quite welcome. There was a time when I needed lots of help too.

That's what makes tek-tips so good. People initially come here for help and then eventually end up helping other people.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom