FoxPro Help and Support - SQL query with "Inner" and "Outer" confusion

Author

SQL query with "Inner" and "Outer" confusion

Barley Man

2005-10-30, 11:25 am

This must be a simple one but I’ll explain it carefully to reduce any
ambiguity:-

A hospital has 4 tables which are related in different ways:-

Each Patient has details (name address etc.):-

PATIENT
Patient.Parefno: Patient’s reference number
patient.paname: Patient’s name

********************
*

Patient may be given a treatment (or not)

TREATMENT
Treatment.trrefno: Treatment Reference number
Treatment.parefno: Reference number of PATIENT who had this treatment
Treatment.trdesc: Description of treatment
********************
*

Each treatment may then have ‘followup’ questionaire over a period on 0, 1
or more occasions. Followup is linked to treatment by trrefno

FOLLOWUP
Followup.Forefno: Followup’s reference number
Followup.trrefno: reference number of TREATMENT for which this is a followup
Followup.foFodate: Date of followup
Followup.strefno: Reference number of STAFF who did followup
********************
*

Each followup is conducted by a member of staff. Staff are linked to
followup by strefno.

STAFF
staff.strefno: Reference number of staff member
staff.name: name of staff member
********************
*

How do I use the Select statement to:-

Produce a table of all patients with all their treatments they received (if
any) plus all associated follow-ups (if any) of each treatment BUT including
the name of the staff member who carried out the ‘followup’.

If there are 100 patients and only 10 of those had 2 treatments and each of
these treatments had 2 followups. I’d expect to produce a table containing
130 items in the list (90 patients with no treatments or follow ups, plus 10
patients with 2 treatments with 2 followups each = 90+ 40 = 130).

If I didn’t need the staff member’s names, I’d be OK with ‘Left joins’ but
when I link the “staff” I get ian ncorrect numbers of entries in the table.
The Staff are ONLY linked to the follow-ups and that seems to cause the
problem. A LEFT join to the staff gives me every member of staff for all
followups and a RIGHT join only gives me the only the patients who have HAD
followups.

ian

Cindy Winegarden

2005-10-30, 11:25 am

Hi Ian,

It sounds like you've already tried code like the code below but you didn't
get what you wanted. What code exactly did you use?

*-- Get all patients, any treatments they had,
*-- any follow-ups to those treatments, and
*-- name of staff who did the treatment.
Select ;
Patient.Whatever, ;
Treatment.Whatever, ;
FollowUp.Whatever, ;
Staff.Whatever ;
From ;
Patient ;
Left Join Treatment On Patient.Parefno = Treatment.trrefno ;
Left Join FollowUp On Treatment.trrefno = Followup.trrefno ;
Left Join Staff On Followup.strefno = Staff.strefno

You could also use an Inner Join on Staff if you can be sure there is always
a staff member recorded with the followup.

--
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@msn
.com www.cindywinegarden.com
Blog: http://spaces.msn.com/members/cindywinegarden

"Barley Man" < BarleyMan@discussion
s.microsoft.com> wrote in message
news:D6F9FE51-A91A-4312-97BC- 20DDEFCD03DF@microso
ft.com...
> PATIENT
> Patient.Parefno: Patient's reference number
> patient.paname: Patient's name
>
> ********************
*
>
> Patient may be given a treatment (or not)
>
> TREATMENT
> Treatment.trrefno: Treatment Reference number
> Treatment.parefno: Reference number of PATIENT who had this treatment
> Treatment.trdesc: Description of treatment
> ********************
*
>
> Each treatment may then have 'followup' questionaire over a period on 0, 1
> or more occasions. Followup is linked to treatment by trrefno
>
> FOLLOWUP
> Followup.Forefno: Followup's reference number
> Followup.trrefno: reference number of TREATMENT for which this is a
> followup
> Followup.foFodate: Date of followup
> Followup.strefno: Reference number of STAFF who did followup
> ********************
*
>
> Each followup is conducted by a member of staff. Staff are linked to
> followup by strefno.
>
> STAFF
> staff.strefno: Reference number of staff member
> staff.name: name of staff member
> ********************
*

> Produce a table of all patients with all their treatments they received
> (if
> any) plus all associated follow-ups (if any) of each treatment BUT
> including
> the name of the staff member who carried out the 'followup'.
>
> If there are 100 patients and only 10 of those had 2 treatments and each
> of
> these treatments had 2 followups. I'd expect to produce a table containing
> 130 items in the list (90 patients with no treatments or follow ups, plus
> 10
> patients with 2 treatments with 2 followups each = 90+ 40 = 130).
>
> If I didn't need the staff member's names, I'd be OK with 'Left joins' but
> when I link the "staff" I get ian ncorrect numbers of entries in the
> table.
> The Staff are ONLY linked to the follow-ups and that seems to cause the
> problem. A LEFT join to the staff gives me every member of staff for all
> followups and a RIGHT join only gives me the only the patients who have
> HAD
> followups.
>
> ian
>

Barley Man

2005-10-30, 8:24 pm

Thanks Cindy

I used exactly that code but:-

Left Join Treatment On Patient.Parefno = Treatment.parefno ;
Left Join FollowUp On Treatment.trrefno = Followup.trrefno ;
Left Join Staff On Followup.strefno = Staff.strefno

......gives far too many records. For every followup, I get a number of
additional records equal to the number of members of staff for every follow
up that occurred

********************
*

Left Join Treatment On Patient.Parefno = Treatment.parefno ;
Left Join FollowUp On Treatment.trrefno = Followup.trrefno ;
inner Join Staff On Followup.strefno = Staff.strefno

...... I get far too few records being only those who have HAD a followup
multiplied by the number of staff members, i.e., it misses out all those
patients who haven't had treatments (and thus haven't had followups).

*****************

Right Join Staff....

is the same as Inner

********************

but

OUTER Join Staff

refuses to work at ALL!

I utterly HATE sql queries! Everyone tells me that you can do ANYTHING in
SQL! Well I can't!

Your help is appreciated even if it only shows that sql queries CAN'T do
everything!

Ian

"Cindy Winegarden" wrote:

> Hi Ian,
>
> It sounds like you've already tried code like the code below but you didn't
> get what you wanted. What code exactly did you use?
>
> *-- Get all patients, any treatments they had,
> *-- any follow-ups to those treatments, and
> *-- name of staff who did the treatment.
> Select ;
> Patient.Whatever, ;
> Treatment.Whatever, ;
> FollowUp.Whatever, ;
> Staff.Whatever ;
> From ;
> Patient ;
> Left Join Treatment On Patient.Parefno = Treatment.trrefno ;
> Left Join FollowUp On Treatment.trrefno = Followup.trrefno ;
> Left Join Staff On Followup.strefno = Staff.strefno
>
> You could also use an Inner Join on Staff if you can be sure there is always
> a staff member recorded with the followup.
>
> --
> Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
> cindy_winegarden@msn
.com www.cindywinegarden.com
> Blog: http://spaces.msn.com/members/cindywinegarden
>
>
> "Barley Man" < BarleyMan@discussion
s.microsoft.com> wrote in message
> news:D6F9FE51-A91A-4312-97BC- 20DDEFCD03DF@microso
ft.com...
>
>
>
>

Cindy Winegarden

2005-10-31, 3:24 am

Hi Ian,

You've said what you don't want, but what exactly do you want?

If a patient has two treatments, one with one follow-up by one staff member
and the other with two follow-ups by two different staff members, what do
you want to see? Do you perhaps want to group on the staff members so if one
staff person did two follow-ups you would only get one row for that person?

That is, do you want to show every follow-up that a patient had, and do you
want to show every staff person that participated in _each_ follow-up, or
every one that participated in any follow-up and show them only once?

Success with SQL develops over time. It takes knowing a "bag of tricks" and
how to apply them to the data you have and what questions you are trying to
answer.

--
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@msn
.com www.cindywinegarden.com
Blog: http://spaces.msn.com/members/cindywinegarden

"Barley Man" < BarleyMan@discussion
s.microsoft.com> wrote in message
news:46EBE841-A674-46E4-AA72- FA9F513365C0@microso
ft.com...

> Left Join Treatment On Patient.Parefno = Treatment.parefno ;
> Left Join FollowUp On Treatment.trrefno = Followup.trrefno ;
> Left Join Staff On Followup.strefno = Staff.strefno
>
> .....gives far too many records. For every followup, I get a number of
> additional records equal to the number of members of staff for every
> follow
> up that occurred

> I utterly HATE sql queries! Everyone tells me that you can do ANYTHING in
> SQL! Well I can't!

Barley Man

2005-10-31, 7:24 am

Sorry Cindy, I'd tried so hard to be clear.

Effectively, I want to treat the Staff record as being appended, directly on
the Followup record. I.e., Each followup record would be associated with 1
staff record. It should be as though the name of the staff member who
conducted the followup was stored WITHIN the followup record. What I actually
need is:-

#A record for each patient
#If the patient has 1 or more treatments, I want a additional* records for
each of those
#If those treatments have followups, I want an additioanl* record for each
of those

* (Obviously, in all the 'additional' cases, the first of those will
effectively be a 'correction' of the 'bare' patient' record and/or 'bare'
patient/treatment record.)

#Finally, on each Followup record, I want to show the name of the staff who
conducted the followup.

Yes, I know I could simply have included the name of the staff member WITHIN
the followup record rather than attaching it to it via a refno. However, this
is a simplified statement of the 'task' and I need a lot more from each of
the tables about each record but have bolied it down to the basics for this
question.

Your help and interest is much appreciated. I know most people say that but
it REALLY is by me!

Ian

"Cindy Winegarden" wrote:

> Hi Ian,
>
> You've said what you don't want, but what exactly do you want?
>
> If a patient has two treatments, one with one follow-up by one staff member
> and the other with two follow-ups by two different staff members, what do
> you want to see? Do you perhaps want to group on the staff members so if one
> staff person did two follow-ups you would only get one row for that person?
>
> That is, do you want to show every follow-up that a patient had, and do you
> want to show every staff person that participated in _each_ follow-up, or
> every one that participated in any follow-up and show them only once?
>
> Success with SQL develops over time. It takes knowing a "bag of tricks" and
> how to apply them to the data you have and what questions you are trying to
> answer.
>
> --
> Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
> cindy_winegarden@msn
.com www.cindywinegarden.com
> Blog: http://spaces.msn.com/members/cindywinegarden
>
>
> "Barley Man" < BarleyMan@discussion
s.microsoft.com> wrote in message
> news:46EBE841-A674-46E4-AA72- FA9F513365C0@microso
ft.com...
>
>
>
>
>

Anders

2005-10-31, 7:24 am

If there are an new row in Followup for every staff person connected to the
followup your query will get those rows.
You shouldn't get row for staff members not individually personally
connected to that particular follow-up.
Is Staff.strefno a primary key for a single individual?
What does
SELECT strefno , COUNT(*) FROM Staff GROUP BY strefno ;
HAVING COUNT(*) > 1
It should return an empty query result set.

-Anders

"Barley Man" < BarleyMan@discussion
s.microsoft.com> skrev i meddelandet
news:46EBE841-A674-46E4-AA72- FA9F513365C0@microso
ft.com...[color=darkred]
> Thanks Cindy
>
> I used exactly that code but:-
>
> Left Join Treatment On Patient.Parefno = Treatment.parefno ;
> Left Join FollowUp On Treatment.trrefno = Followup.trrefno ;
> Left Join Staff On Followup.strefno = Staff.strefno
>
> .....gives far too many records. For every followup, I get a number of
> additional records equal to the number of members of staff for every
> follow
> up that occurred
>
> ********************
*
>
> Left Join Treatment On Patient.Parefno = Treatment.parefno ;
> Left Join FollowUp On Treatment.trrefno = Followup.trrefno ;
> inner Join Staff On Followup.strefno = Staff.strefno
>
> ..... I get far too few records being only those who have HAD a followup
> multiplied by the number of staff members, i.e., it misses out all those
> patients who haven't had treatments (and thus haven't had followups).
>
> *****************
>
> Right Join Staff....
>
> is the same as Inner
>
> ********************

> but
>
> OUTER Join Staff
>
> refuses to work at ALL!
>
> I utterly HATE sql queries! Everyone tells me that you can do ANYTHING in
> SQL! Well I can't!
>
> Your help is appreciated even if it only shows that sql queries CAN'T do
> everything!
>
> Ian
>
>
>
>
> "Cindy Winegarden" wrote:
>

Anders

2005-10-31, 7:24 am

If ten people attended to the patient on a followup and each one registered
his presence by adding a row in the Followup table, do you then want all ten
rows/names?
-Anders

"Barley Man" < BarleyMan@discussion
s.microsoft.com> skrev i meddelandet
news:FB8B5BF8-C4DC-4F77-ADA0- FB5631551977@microso
ft.com...[color=darkred]
> Sorry Cindy, I'd tried so hard to be clear.
>
> Effectively, I want to treat the Staff record as being appended, directly
> on
> the Followup record. I.e., Each followup record would be associated with 1
> staff record. It should be as though the name of the staff member who
> conducted the followup was stored WITHIN the followup record. What I
> actually
> need is:-
>
> #A record for each patient
> #If the patient has 1 or more treatments, I want a additional* records for
> each of those
> #If those treatments have followups, I want an additioanl* record for each
> of those
>
> * (Obviously, in all the 'additional' cases, the first of those will
> effectively be a 'correction' of the 'bare' patient' record and/or 'bare'
> patient/treatment record.)
>
> #Finally, on each Followup record, I want to show the name of the staff
> who
> conducted the followup.
>
> Yes, I know I could simply have included the name of the staff member
> WITHIN
> the followup record rather than attaching it to it via a refno. However,
> this
> is a simplified statement of the 'task' and I need a lot more from each of
> the tables about each record but have bolied it down to the basics for
> this
> question.
>
> Your help and interest is much appreciated. I know most people say that
> but
> it REALLY is by me!
>
> Ian
>
> "Cindy Winegarden" wrote:
>

Barley Man

2005-10-31, 7:24 am

Hi Anders

No. Only one person (staff member) can conduct any one SINGLE followup.
I.e., it only takes one staff member to do a complete floowup, thus the staff
have (in this case) a one-to-one relationship to followups

Ian

"Anders" wrote:

> If ten people attended to the patient on a followup and each one registered
> his presence by adding a row in the Followup table, do you then want all ten
> rows/names?
> -Anders
>
> "Barley Man" < BarleyMan@discussion
s.microsoft.com> skrev i meddelandet
> news:FB8B5BF8-C4DC-4F77-ADA0- FB5631551977@microso
ft.com...
>
>
>

Sponsored Links