Consolidating values from multiple tables

Question

I have an application which has data spread accross 2 tables.

There is a main table Main which has columns - Id , Name, Type. Now there is a Sub Main table that has columns - MainId(FK), StartDate,Enddate,city and this is a 1 to many relation (each main can have multiple entries in submain).

Now I want to display columns Main.Id, City( as comma seperated from various rows for that main item from submain), min of start date(from submain for that main item) and max of enddate( from sub main).

I thought of having a function but that will slow things up since there will be 100k records. Is there some other way of doing this. btw the application is in asp.net. Can we have a sql query or some linq kind of thing ?

Answer 1

This is off the top of my head, but firstly I would suggest you create a user defined function in sql to create the city comma separated list string that accepts @mainid, then does the following:

DECLARE @listStr VARCHAR(MAX)
SELECT @listStr = COALESCE(@listStr+',' , '') + city
FROM submain
WHERE mainid = @mainid

... and then return @listStr which will now be a comma separated list of cities. Let's say you call your function MainIDCityStringGet()

Then for your final result you can simply execute the following

select cts.mainid,
       cts.cities,
       sts.minstartdate,
       sts.maxenddate
  from ( select distinct mainid,
                         dbo.MainIDCityStringGet(mainid) as 'cities'
           from submain) as cts
         join
       ( select mainid,
                min(startdate) as 'minstartdate',
                max(enddate)   as 'maxenddate'
           from submain
          group by mainid ) as sts on sts.mainid = cts.mainid
 where startdate <is what you want it to be>
   and enddate   <is what you want it to be>

Depending on how exactly you would like to filter by startdate and enddate you may need to put the where filter within each subquery and in the second subquery in the join you may then need to use the HAVING grouped filter. You did not clearly state the nature of your filter.

I hope that helps.

This will of course be in stored procedure. May need some debugging.

Answer 2

An alternative to creating a stored procedure is performing the complex operations on the client side. (untested):

var result = (from main in context.Main
              join sub in context.SubMain on main.Id equals sub.MainId into subs
              let StartDate = subs.Min(s => s.StartDate)
              let EndDate = subs.Max(s => s.EndDate)
              let Cities = subs.Select(s => s.City).Distinct()
              select new { main.Id, main.Name, main.Type, StartDate, EndDate, Cities })
             .ToList()
             .Select(x => new
                          {
                              x.Id,
                              x.Name,
                              x.Type,
                              x.StartDate,
                              x.EndDate,
                              Cities = string.Join(", ", x.Cities.ToArray())
                          })
             .ToList();

Answer 3

I am unsure how well this is supported in other implimentations of SQL, but if you have SQL Server this works a charm for this type of scenario.

As a disclaimer I would like to add that I am not the originator of this technique. But I immediately thought of this question when I came across it.

Example:

For a table

Item ID       Item Value       Item Text
-----------  ----------------- ---------------
1             2                 A 
1             2                 B
1             6                 C
2             2                 D
2             4                 A
3             7                 B
3             1                 D

If you want the following output, with the strings concatenated and the value summed.

Item ID      Item Value        Item Text
-----------  ----------------- ---------------
1            10                 A, B, C 
2            6                  D, A
3            8                  B, D

The following avoids a multi-statement looping solution:

if object_id('Items') is not null
  drop table Items
go

create table Items
     ( ItemId    int identity(1,1),
       ItemNo    int not null,
       ItemValue int not null,
       ItemDesc  nvarchar(500) )

insert Items
     ( ItemNo,
       ItemValue,
       ItemDesc )
values ( 1, 2, 'A'),
       ( 1, 2, 'B'),
       ( 1, 6, 'C'),
       ( 2, 2, 'D'),
       ( 2, 4, 'A'),
       ( 3, 7, 'B'),
       ( 3, 1, 'D')

select it1.ItemNo,
       sum(it1.ItemValue) as ItemValues,
       stuff((select ', ' + it2.ItemDesc  --// Stuff is just used to remove the first 2 characters, instead of a substring.
                from Items it2 with (nolock)
               where it1.ItemNo = it2.ItemNo
                 for xml path(''), type).value('.','varchar(max)'), 1, 2, '') as ItemDescs --// Does the actual concatenation..  
  from Items it1 with (nolock)
 group by it1.ItemNo

So you see all you need is a sub query in your select that retrieves a set of all the values you need to concatenate and then use the FOR XML PATH command in that sub query in a clever way. It does not matter where the values you need to concatenate comes from you just need to retrieve them using the sub query.