Well, i'm new to asp.net, and now i'm stuck ..
My main problem is that i need to select data from few tables using their relationships as defined in database.
Let's presume i've got 2 tables, tblUsers, and tblAddress
tblUsers fields: userId, userName
tblAddress: adrrId, userId, addressStr
it has 1:N relationship -
basicly, i want to select few rows from tblUsers :
"SELECT * FROM tblUsers WHERE userName='pirate'"
then i fill dataset.tblUsers with the results.
I've got more then 1 user from tblUsers, and i allready have a pre-defined dataset with the proper relations defined.
Now, i need to only the relevant rows from tblAddress. Meaning that i don't want entries from tblAddress where userId is not one of the userId's returned by the previous query..
Is there any reasonable, non complex way to do so?
My current options are:
1. Loop over dataset.tblUsers and performing 'n' select queries: "SELECT * FROM tblAddress WHERE userId =" + dataSet.tblUsers.rows
... (if i recall correctly), then joining them up together in one dataTable
2. Using IN operator with select inside a select: "SELECT * from tblAddress WHERE userId IN (SELECT userId from tblUsers)"
3. Pulling all the data, with the irelevant one aswell. then using relations to filter out the irelevant data on code-behind level.
All options seem to be rather bad for me, since they eat up alot of unneccesarry system resources (the database is rather big)
While the 1 and 2 seem to have N*N complexity, the 3rd seem to be passing alot of irelevant data...
Is there any shortcut? any using relations inside the database?
Please help ..
Thanks in advance :)