General SQL - Query Master/Detail Tables
Posted on 1997-09-27
I have two tables in an Master-Detail relationship, Activities and Locations respectively. As the names imply, the Activities table contains things to do, such as Restaurants, Parks, Plays, Movies, etc. The Locations table contains all locations for an Activity if there is MORE than one location. The master table has an Address and a City field. If there is only one location, the address & city is stored on the Mater Table. If there is more than one location, "Various" is inserted in the Address & City fields on the Master table and the 'real' addresses and Cities for that Activity are stored in the Locations table.
Now, how do I find a particular CITY in EITHER table using SQL or some other method? Below is the English translation of what I want to do.
****Find every MASTER record where they have a Location in "SOME CITY".****
This of course would require the query to search the master table for SOME CITY then search the Locations table for SOME CITY - and if either were found - select its master record.
I'm looking for someone to correct my query string OR tell me a better way of doing this. I tried this SQL string below in Delphi, Paradox, and Access 97 - and all of the applications hung - so I don't know if this works or not. I have about 3,000 records in both the Master and Detail tables, but all fields being searched are indexed (although I can't figure out if Delphi uses the index or not). The tables are Paradox 4.5/5.0 tables locally in D2 Win95, although I don't think that matters since this is really an SQL issue, not necessarily Delphi.
Note: Name & State are the Primary Key & Referential Integrity Links
SELECT DISTINCT D.Name, D.State, D.Address, D.City
FROM "Activity.DB" D, "Locations.DB" D1
(D.City = 'Atlanta')
AND (D1.Name = D.Name)
AND (D1.State = D.State)
OR (D1.City = 'Atlanta');