QUESTION: Using XBase to construct a one to many query 
Author Message
 QUESTION: Using XBase to construct a one to many query

Hi all,

A query for all you XBase developers out there.  I've been having a real
difficult time relating several tables to achieve a One To Many using
XBase calls.

I have two tables, Business and Bus_Cats (business category id's).

*BUSINESS*
Bus_ID
Name
Street  etc

*BUS_CATS*
Bus_ID
Cat_ID

I'll phrase the question two ways (for those that do and don't use SQL).

For those that know SQL, what I want to do is:
SELECT DISTINCT BUSINESS.NAME, BUSINESS.STREET FROM BUSINESS, BUS_CATS
WHERE STREET =  "BONYTHON CR" AND BUS_CAT.CAT_ID = 2041 AND
BUSINESS.BUS_ID = BUS_CATS.BUS_ID;

For those that don't use SQL, what I need to do is return a list of
businesses which belong to a particular category ID, further filtered by
such things as street address etc.

So if a business belongs to more than one category ID (Businesses and
Categories are many to many) then it appears only once in the query
results (for filling a browse control).

There can be many records in the Bus_Cats table corresponding to each
business (linked by the ID of the business record).

Now in SQL this would be childs play, but I am a newby to XBase.
SetRelation does not appear to do the trick as seems to move the record
pointer to the first matching BUS_CAT record, it won't go the other way
and find all the right businesses (as I understand it, although I would be
happy to learn that I am wrong. It will save me a lot of work).

If anyone can help me I would be very grateful.

Galen White



Thu, 03 Sep 1998 03:00:00 GMT  
 QUESTION: Using XBase to construct a one to many query

: A query for all you XBase developers out there.  I've been having a real
: difficult time relating several tables to achieve a One To Many using
: XBase calls.
snip
: For those that know SQL, what I want to do is:
: SELECT DISTINCT BUSINESS.NAME, BUSINESS.STREET FROM BUSINESS, BUS_CATS
: WHERE STREET =  "BONYTHON CR" AND BUS_CAT.CAT_ID = 2041 AND
: BUSINESS.BUS_ID = BUS_CATS.BUS_ID;
snip

 It depends what brand of XBase you use I suppose.  FoxPro has pretty
good SQL capabilities and should be able to execute the query as it's
written - I don't see any errors.  I'm not sure but I believe that dBASE
has some SQL too.  And if you know SQL you will find it far easier to use
that than to learn how to set up relations and filters to accomplish the
same thing using xBASE commands in this instance.



Fri, 04 Sep 1998 03:00:00 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. xBase Glossary/Language constructs needed

2. Sql Query - One Simple Question

3. One To One and Many Question

4. PLEASE HELP (Trying to construct Report...)

5. Problem using xBASE to ftp files

6. Changing the cp of a DBF file used by DOS XBase apps and VFP

7. Follow up question to previous thread on VFP 5.0a query questions

8. Follow up question to previous thread on VFP 5.0a query questions

9. xbase dinosaur questions

10. Two Queries to One Report

11. Max Row of aCertain Code of a One to Many Query

12. use query by example to remove rec from one and append to another

 

 
Powered by phpBB® Forum Software