New topic: Query Across Multiple Tables
<http://forums.realsoftware.com/viewtopic.php?t=30458> Page 1 of 1 [ 1 post ] Previous topic | Next topic Author Message ifonline Post subject: Query Across Multiple TablesPosted: Tue Oct 13, 2009 10:07 am Joined: Thu Mar 05, 2009 4:02 pm Posts: 28 I am having trouble wrapping my head around how to get the results I am looking for, but I will do my best to describe the goal. First, here is an image of my program in development: My program has one local RB-SQL database with four tables (one each for Contract Data, Financial Data, Dealers, and Reinsurance Companies). For this situation, I will focus on the last two tables (Dealers and Reinsurance Companies), and will be focused on searching the Dealers table specifically. The structure for Dealers is: - RecordID - DealerName - DealerCode - ReinsuranceCompanyRecordID The structure for Reinsurance Companies is: - RecordID - ReinsuranceCompanyName - ReinsuranceCompanyCode I have no problems searching with SQL statements within one table, like: Code:"SELECT * FROM DealerData WHERE DealerName LIKE '%" + SearchString + "%'" With this SQL statement, as I type in the search field, the list is reduced to only matching records. This part works fine and as expected, but only works for Dealer Name and Dealer Code as those are actual text values stored in the Dealers table. However, the trouble comes in when I want to search the Dealers for Reinsurance Companies. In other words, I have my list of Dealers and want to reduce that list to all dealers with the Reinsurance Company named "X". And therein is the problem: how do I do this? The Dealers table stored a Record ID for the Reinsurance Company associated with a Dealer, and when the list is populated, each list row cross-references the Reinsurance Companies table to extract the appropriate Reinsurance Company Name and Code to display in the list for each Dealer. But because I am storing a Record ID (a number stored as a string) in the Dealers table to facilitate that cross-referenced link to the Reinsurance Companies table, I can't search for the Reinsurance Company Name within the Dealers table (because the actual name for the Reinsurance Company is not stored within the Dealers table. I want to keep the relationship between the two tables as is (assuming this is the best way, of course) because my logic is this: if I find a typo in a Reinsurance Company name and alter that record in the Reinsurance Companies table to make the correction, this change will be reflected when I refresh the list of Dealers. I hope this makes sense, and I hope someone can help me out with this one. I expect it is an easy solution, I just can't seem to get it. Likely I just can't see the forest for the trees as they say. Thanks. _________________ Ian Top Display posts from previous: All posts1 day7 days2 weeks1 month3 months6 months1 year Sort by AuthorPost timeSubject AscendingDescending Page 1 of 1 [ 1 post ] -- Over 1500 classes with 29000 functions in one REALbasic plug-in collection. The Monkeybread Software Realbasic Plugin v9.3. http://www.monkeybreadsoftware.de/realbasic/plugins.shtml [email protected]
