Retrieving data from multiple SQL tables with multiple related entries

I have a database with three tables, related with foreign keys:

Trips:

ID
Number
Date

Days:

ID
TripID (Foreign key to ID column in trips)
Date
Start
End

Legs:

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.

Answers


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.


Need Your Help

jericho-html - text extracting and incorrect text lenght

java gwt html-parsing jericho-html-parser

Today I tried to use the lib as jericho-html-3.2 to extract text from simple html... And I faced a strange text fake length problem as follows:

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.