Help with entity/VB code to get data from multiple tables and set to string
tblTeams (cols are ID, Team, RegionName (this is an int), URL)
and
tblRegions (cols are ID, RegionName)
I want to write some code that first puts every entry of tblRegions into a list
For each item in that list, I want to find each Team and URL from tblTeams where tblRegions.ID = tblTeams.RegionName and add the values to a string.
I have a model setup with the 2 tables and need to do this in VB.NET/Entity. I've gotten so far as to get the first part in a list and cycle through it, but I think I may be overcomplicated things. To help understand, I'm trying to make a dropdown navigation menu where the tblRegions.RegionName are the first level, and the tblTeams data are the submenu where the aforementioned where clause is met.
Honestly if I could just have a string that looked something like...
Region1 TeamR1a R1aURL TeamR1b R1bURL TeamR1c R1cURL Region2 TeamR2a R2aURL TeamR2b R2bURL Region3 TeamR3a R3aURL
That would actually work for me.
Developer
I would bite the chunk in small pieces. If you are proficient at it (very simple, actually) write a scalar function which would return the string you are referring to, something like this:
declare @returnValue varchar(max) = '';
select @returnValue += Team + ' ' + URL + ' '
from tblTeams
where RegionName = @RegionName;
where @RegionName will be passed as a parameter. Issue resolved for the 1 row in the table.
Should you need entire result set in one use following, assuming above query is in function fnGetTeams:
select RegionName, t.ReturnValue
from tblRegions r
outer apply [dbo].[fnGetTeams](r.RegionName) t
Please give more meaningful names.
Step 2. Iterate through each result from step1, pull all Team and URL from tblTeams where tblTeams.RegionName = the ID being iterated through
Step 3. Add the values of Team and URL from step 2 to a string (i.e. strTest = strTest & teamvalue & urlvalue)
I for sure do NOT want everything in one go. I need to pull all of the regions and then each team associated with said region.
More meaningful...
Northeast
Mets
Yankees
Red Sox
Southwest
Diamondbacks
Dodgers
Southeast
Marlins
Rangers
Braves
Looking for Entity help here, not really SQL syntax. I'm very new to using data models. An example of doing this or something similar is what I'm looking for.
Gain unlimited access to on-demand training courses with an Experts Exchange subscription.
Get AccessWhy Experts Exchange?
Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.
When asked, what has been your best career decision?
Deciding to stick with EE.
Being involved with EE helped me to grow personally and professionally.
Did You Know?
We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE
Ask ANY Question
Connect with Certified Experts to gain insight and support on specific technology challenges including:
- Troubleshooting
- Research
- Professional Opinions