Help with SQL Select please :)

[stanmurphy]

Hello,

I'm working in VFP 7.0 SP1.

I have two tables, APPOINTMENTS and COUNSELLORS.

The APPOINTMENTS table has a field - CounID - that corresponds to the CounID key field in COUNSELLORS.

How would I build a Select statement that would take two fields from APPOINTMENTS and the contents of the Counsellor_name field in COUNSELLORS and put the three fields into a cursor.

For instance:

APPOINTMENTS has a record with client name, appointment date and counsellor number: John Smith, 02/21/2002, 2

I want to take John Smith, 02/21/2002 and the name that corresponds to CounID=2 in the COUNSELLOR file and put the three fields into a cursor. I have created a persistant relationship between the APPOINTMENTS table and the COUNSELLOR table based on CounID.

Any insight would be appreciated.

Stan

 

[rgbean]

Stan,

SELECT a.clientname, a.appdate, c.counsellor ;
 FROM APPOINTMENTS a, COUNSELLORS c ;
 WHERE a.counID = c.counID ;
 INTO cursor curTemp

Rick

 

[stanmurphy]

Thanks for the reply.

I wasn't entirely clear in my question.

This is my current select statement.

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

m.client = thisform.grid1.column1.text1.Text && The highlighted client

SELECT x.Clientid, x.Apptdate, y.Counname from appointments x, counsellor y;
WHERE x.clientid = m.client INTO CURSOR curAppoint

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

I'm selecting from the APPOINTMENTS table based on the value of m.client

What's happening is that if there are two Appointment records and 7 Counsellor records, I get 7 records returned for each Appointment

 

[rgbean]

Stan,
The simplest change is to "combine" the two selects:

SELECT x.Clientid, x.Apptdate, y.Counname ;
 FROM appointments x, counsellor y;
 WHERE x.clientid = m.client ;
   AND x.clientid = y.clientid ;
 INTO CURSOR curAppoint

Rick

 

[stanmurphy]

The counsellor table is keyed on CounID, not ClientID. So what I need is, if the CounID in the Appointment is 2, I need the Select to look up 2 in the Counsellor table and return the contents of the COUNNAME field, along with the fields from APPOINTMENT. Is this possible?

 

[danceman]

SELECT x.Clientid, x.Apptdate, y.Counname ;
from appointments x left join counsellor y;
on x.clientid = y.clientid;
WHERE x.clientid = m.client INTO CURSOR curAppoint

look up select sql in the help file and study the use of joins. that is the prefered method to join tables. the where is for the selection criteria. Attitude is Everything

 

[stanmurphy]

I had to change the 'on' part as shown below because that is the field I wanted to look up in the second table, but it works like a charm. Thanks very much for your help Rick!!!

*******************************************
SELECT x.Clientid, x.Apptdate, y.Counname ;
from appointments x left join counsellor y;
on x.counid = y.counid;
WHERE x.clientid = m.client INTO CURSOR curAppoint
******************************************************

I'll go study joins now to figure out what it's doing

 

[danceman]

sorry about the counid.

this is not Rick, Attitude is Everything

 

[stanmurphy]

Oops !! Sorry about that. I should have checked the header

Thanks again.

 

[rgbean]

Thanks Danceman, while I may have gotten written credit this time (at least to start with), at least Stan got the answer finally. (I missed the subtle counid vs. clientid difference in the reply.)

Rick