(VB.NET) How to load dataset's child tables only with records matching parent table records?

I want to load a dataset so that the only rows loaded in the child tables are those with a matching parent record. I do not want to load everything into the dataset and filter data. I want a dataset with the data for one project in it.
For example:

A building exists at 123 Main St with 5 units, each unit then has 8 rooms
So I would have the following tables: tBulding, tUnits,tRooms
tBuilding could have many records.
How could I create a dataset with just the info for 123 Main St, instead of loading a dataset with all the records and then filtering it.
I would like to keep the data in its separate tables rather than try to load one giant table.
The idea is to send the data for the single building electronically.

I know how to load the (parent) tBuilding data properly but how can I load only the rows in the child tables with matching parent records? I thought that some simple method would be available to perform this but I cannot locate one in any of the many books I have.
This process would be performed in a VB.Net Compact app running on a pocket pc so using memory efficiently is a consideration.
Thanks in advance for the help.
happyman14Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeff CertainCommented:
1. Are the parent records and child records from the same table?
2. How many levels of parent-child relationship do you need to maintain?
3. Can you provide the design for the relevant table(s)?
4. What database are you using?
5. Are you using stored procedures or dynamic SQL?
happyman14Author Commented:
1)No the parent and child records are in separate tables
2) I actually have about 4 levels of parent-child relationships.There are also 4 tables below the tRooms table using RoomID as foreign key
3) tBuilding fields:(GUID PrimaryKey) BuildID,StreetNo,Street,City,State
    tUnits fields: (GUID PrimaryKey) UnitID,(ForeighKey)BuildID, StreetNo,Street,Unit, City,State
    tRooms fields (GUID PrimaryKet RoomID, (ForeighKey)UnitID, UnitNo
(simplification of  more complex tables)
4) Using .cdb database on pocket pc and Access 2000 on desktop (GUID is actually text field storing GUID Value
 5Dynamic SQL
   

Jeff CertainCommented:
Let me clarify... you want only the data from the child table for a particular parent record.

Does "SELECT * FROM ChildTable WHERE ParentId=yourparentID" work? Or have I overly simplified the problem?

You can even populate multiple tables at once, using a dataadapter:
"SELECT * FROM parentTable WHERE ParentID=thisParentID;SELECT * FROM childTAble WHERE ParentID=thisParentId"
PMI ACP® Project Management

Prepare for the PMI Agile Certified Practitioner (PMI-ACP)® exam, which formally recognizes your knowledge of agile principles and your skill with agile techniques.

happyman14Author Commented:
If I were only working with one level of parent-child relationship this would be fine but for each building I now have 5 unit records. I must then get 8 room records for each of these 5 units loaded int tRooms. Then I would have to get records from four tables (Child tables of tRooms)  for each of the 40 rooms.
Jeff CertainCommented:
How about one (sneaky) table to work from? You'll have to create columns by hand (no autogenerate) but...

SELECT b.BuildID, b.StreetNo, b.Street, b.City, b.State, u.UnitId, u.Unit, r.RommId, r.UnitNo FROM
tBuilding b
INNER JOIN tUnits u ON u.BuildId=b.BuildId
INNER JOIN tRooms r ON r.UnitId=u.UnitId
WHERE b.BuildId=parentId

happyman14Author Commented:
Sorry I forgot to clarify, I am trying to get only the data from the child table as well as the data from the child tables of the original child table going down several levels
Jeff CertainCommented:
So you're going to pick the building ID and you want all the child (and grandchildren, etc) records?

If one table is sufficent, you can do this:
SELECT  u.UnitId, u.StreetNo, u.Street, u.City, u.State, u.Unit, r.RoomId, r.UnitNo FROM
tBuilding b
INNER JOIN tUnits u ON u.BuildId=b.BuildId
INNER JOIN tRooms r ON r.UnitId=u.UnitId
WHERE b.BuildId=parentId

Other wise:
Table1
SELECT  u.* FROM
tBuilding b
INNER JOIN tUnits u ON u.BuildId=b.BuildId
INNER JOIN tRooms r ON r.UnitId=u.UnitId
WHERE b.BuildId=parentId

Table2:
SELECT r.* FROM
tBuilding b
INNER JOIN tUnits u ON u.BuildId=b.BuildId
INNER JOIN tRooms r ON r.UnitId=u.UnitId
WHERE b.BuildId=parentId

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
happyman14Author Commented:
I thought about that but  I wanted to make it easy to use the separate table data (i.e. load it into a datagrid so that the parents and children could be viewed easily on the receiving end.( I am sending the dataset elsewhere) I also wanted to provide the ability to send the tables as separate datasets in case some data irregularity prevented an individual datatable from being converted into a dataset. I am trying to think through how to split up the data at the other end and whether that would be ok. I had originally thought there would be a simple built-in method to create a dataset that would filter all the child records based on the parents and I didn't want to reinvent the wheel.
happyman14Author Commented:
Sorry we are a little out of sync in  the messages. You seem to type (and probably think) faster than I do
Jeff CertainCommented:
You can set relations in the dataset... but I haven't used that much... let me see if I can find anyone who has...
Jeff CertainCommented:
Should I interpret your post at 2:00 MDT as  meaning that my earlier post solves the problem? Or do we still need to explore datarelations?
happyman14Author Commented:
I am worried that the datarelation will create an error as you try to load any unmatching rows in the child tables. It might be possible to trap or skip the rows with errors to load just the appropriate rows. That might be another workable solution.

The solution you present is actually exactly what I thought I would do if another methodology wasn't available. Create each Select statement individually based on inner joins for each datatable. I guess I was just worried that running the individual queries might tax the pocket pc's memory but my concerns are probably not warranted. I can at least give it a try. Sometimes you work so long on this stuff that you question your sanity and logic so it's good to here someone else thinks its a viable idea.
happyman14Author Commented:
I will gladly award the points for your help. I am a little concerned that that multiple queires might tax the pocket pc's memory but I will try it.

 I would also award bonus points if you or anyone else had another idea such as trapping errors created by datarelations when trying to load child rows without matching records in the parent table, skipping unmatching rows and continuing to load any matching rows.

I would like to award the points for your help but I would like to keep the quetion open in case there are other ideas out there. Do you know if this is possible?
Jeff CertainCommented:
Just leave the question open...

As to the datarelations... ummm... shouldn't your database have constraints such that the case you're concerned about should never arise?
happyman14Author Commented:
I would create a data adapter for the child table. I would create the datarelation for the dataset tables fill the data adapter with all the records from the original child table. Since the parent table only has a subset of the records (only one record) most of the rows in the child table will not have a matching record. I am assuming an error will be generated. If this specific error can be trapped and the offending record can be skipped it might be possible to continue on loading only matching records. I am not sure it would work or even if it does it might be more memory inefficient than running separate quieries, using inner joins for each datatable.
happyman14Author Commented:
To answer the previous question more simply. The error would be caused because I am trying to load all the child records into the dataset but the parent only has one record so most child records wouldn't match. It is not a lack of referential integritiy in the database I am talking about, but a deliberate attempt to load a child table in a dataset with records that have no matching record in the parent table. The datarelation would prevent this. Are the child rows loaded one-by-one and would this create a trappable error so that the non-matching records could be skipped and the load process continue to load only matching records?
Jeff CertainCommented:
Ah. Again, when I tried to load the children controls.... I'd filter them using the select statement above to avoid orphaned children...

Guess we're back to where we started...
happyman14Author Commented:
No I think your original solution is viable. I am talking about a possible alternative where you
1) load the dataset with a parent table with only one record
2) Create a dataadapter for the child table, create a datarelation
3) try to load child tables without any where clause so that the datarelation would cause trapable errors for the orphaned rows, skip the orphaned rows and continue loading any matching records

I am not sure if you can create the datarelation before loading the child table. I guess I'm just tired. The more I think about it the more I am inclined to go with your original idea
happyman14Author Commented:
I was trying to think of a programmatic way to solve this type of problem in general without modifying SQL statements for each individual table.
happyman14Author Commented:
Thanks for your help I will award the points now and thank you again for all your speedy  help and replies. I think I'll formulate another similar question to try and find a programmatic way to save a new dataset based on the value selected in an existing dataset that includes only the relevant child records in the original. Thanks again for all your help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.