Avatar of Member_2_1242703

asked on 

Help with entity/VB code to get data from multiple tables and set to string

I have 2 tables:

tblTeams (cols are ID, Team, RegionName (this is an int), URL)
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.
* Entity Framework CoreVisual Basic.NET.NET ProgrammingASP.NETMicrosoft SQL Server

Avatar of undefined
Last Comment
Avatar of Nitin Sontakke
Nitin Sontakke
Flag of India image

I am not TOO sure if i clearly understand this, however, from whatever i understand this is pretty simple. Just one thing which is not clear to me. Do you want an entire result set in one go?

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;

Open in new window

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

Open in new window

Please give more meaningful names.
Avatar of Member_2_1242703


Step 1. Pull all ID from tblRegions
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...

    Red Sox

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.
Mike, is this issue solved?
Avatar of Member_2_1242703

Blurred text
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of Member_2_1242703


Didn't get any answers, came up with an alternative solution.
Microsoft SQL Server
Microsoft SQL Server

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews


IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo