Retrieving data from multiple SQL tables with multiple related entries
I have a database with three tables, related with foreign keys:
ID Number Date
ID TripID (Foreign key to ID column in trips) Date Start End
ID DayID (Foreign key to ID column in days) Origin Destination
In my app, I have a Trip class, Day class, and Leg class corresponding to each table. Each Trip has a list of associated Days, each Day has a list of associated Legs. Currently I load the data from the database by running a query for the desired trip. Then I run another query to fetch all the days associated with that trip and add those, then I run a query for each day to fetch the associated legs and add those.
It seems to me that there should be a more efficient way to accomplish this. Is my only other option doing a triple join of the three tables and getting a cursor that has a row for each leg that also contains all of the day and trip data for that leg? Is there a better way to do it? I'm not too familiar with SQL queries.
Join is the obvious answer:
SELECT * FOM Trips t JOIN Days d ON t.Id = d.TripId JOIN Legs l ON d.Id = l.DayId WHERE t.Id = tripId
You'll need to replace tripId with the id of the trip you're trying to find. A good idea is to also replace * with the actual columns you need returned to your application.