Joins?

[MForrest]

Good Morning All

I have a search screen to locate and retrieve all clients, my client also wishes to do a variety of searches by client which incorporates searching data from seperate tables, 8 project tables, a qualification table and an outcome table (another form of qualification). What is bamboozling me is that my first reaction was to create a cursor for all the tables incoporating clients/projects/qualifications and outcomes and performing my search on this one cursor. As a client is not always part of a project or may not have a qualification/outcome, this big cursor idea will not work. Therefore I realise I need to venture into the world of joins and understand finally what they are about.

What has also crossed my mind is can join types solve my problem or will I have to check what the user wishes to search on and chose one of many select queries dependent upon the search criteria, for example

1. use one query if the user wants to search client criteria only
2. use another query if they wish to search for clients or 1 of 8 projects
3. another query if they wish to search for clients who have a qualification
4. another query if they wish to search for clients who have an outcome

Also other queries built to combine the above 4 options

I hope Im making sense, I have only performed succesful queries on multiple tables when I have a one/one or one to many relationship where there will always be a link. At the moment most of my clients do not have qualifications, outcomes or projects but may have.

 

[Mike Lewis]

Hi MForrest,

I feel sure that, with a lot of effort and ingenuity, you could write a single SELECT that covers all your options. And there are no doubt people here who can tell you how to do that (but I'm not one of them).

My own inclination would be to do it the old fashioned way. First prompt the user for all their choices. Then do a scan loop on the client table. For each client, do a SEEK or LOCATE into the subsidiary tables to find the records, if any, that match the requirements. Then append a record to the output cursor that contains the required fields.

It might run a bit more slowly than a SELECT, but I'll bet you'll code it a lot faster.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com