Complex data structure - how to run the select statement

[markros]

Hi everybody,

I have a table called RouteDetail that has RouteDetailCategoryID field and nID Field.

RouteDetailCategories table has an ID field and a cDescription field which is a table name.

Currently there are 3 records only in the Categories table.

My question is - how should I try to write my select statement (without using dynamic SQL) to relate RouteDetail and the appropriate table based on the CategoryID field and the nID field which is a pointer into that table?

Any ideas of what would be the best approach here?

Thanks in advance.

 

[gmmastros]

A table name stored in a varchar column? yuck.

Since there are only 3 possible choices, I would be tempted to join all 3 to the column, but then hard code the tablename data.

Post some sample data and I'll try to work up a solution for you.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bborissov]

The short answer is: You can't.
But why you should have MANY tables for every different category? W.o knowing you DB architecture I would do this that way:
One table:

Categories:
Id Name

One Table:
CategoryDetaills

CategoryId, PK, OtherNeededFields



And I will save CategoryDetaills.PK in the RouteDetail table, just because I want my select to be easier.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[markros]

nRouteDetailCategoryID	cDescription
1	JobSplit
2	Users
3	Equipment

nRouteDetailID	nCompanyID	nRouteHeaderID	nRouteDetailCategoryID	nID	nRteHeadID	nRtedtlID	cTable
1	1	589	1	3	2	4	JobSplit
2	1	590	1	4	3	6	JobSplit
3	1	591	1	5	4	8	JobSplit
4	1	592	1	6	5	9	JobSplit
5	1	593	1	7	6	10	JobSplit
6	1	594	1	8	7	11	JobSplit
7	1	595	1	9	8	12	JobSplit
8	1	596	1	10	9	13	JobSplit
9	1	597	1	11	10	14	JobSplit
10	1	598	1	12	11	15	JobSplit

The table itself has cTable field as well, but this is a left over from the original conversion.

 

[markros]

Basically, each Route here has many attributes - Users, Jobs Info, Equipment info, may be others.

I believe I saw a recent question on this and even tried to answer it, but now I'm looking at this problem to solve it myself (it is always 100% easier to solve someone's else problems)

 

[gmmastros]

What is the desired output?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

I want to LEFT JOIN with each of these tables and get fields from these tables, e.g. add Users information, add Equipment info, add Job's info.

I'm not yet familiar with the tables structure and relations, so the Users, JobSplit, Equipment tables may need a join to other tables too.

 

[bborissov]

GRRRRRRRRRR,
Who design this??????????????
I thing you are stuck with Dynamic SQL.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[markros]

Not really - but too complex. We actually had similar structure in another application I worked on (VFP based). Before I left, we were thinking of moving it to SQL/.NET

I'm not sure how that database (in that complex application) is going to be converted.

Here is the same case. We have an existing VFP application and we're working on ASP.NET conversion. The original VFP database design was ported to SQL Server with minimal structural changes.

Now, in addition to the problem above, we have Address table, which is designed similarly. It has an integer Key pointing to the table and the Category ID.

So, we have addresses for Jobs, for Customers, for other stuff, etc. in one table.

From the SQL Statement above when I LEFT JOIN to JobSplit I need to go to the JobHeader from it and then get the address info for the job. Not to mention other information.

So, now I'm just contemplating, what would be the best and most efficient way to get the result.

I guess I also post this question in another forum to get more brain storming ideas.

Thanks again, Boris.

 

[bborissov]

OK, in VFP it is an "easy" job. One recursive function and you get all done. But in T-SQL?
And as I mentioned recursive
Did you try CTE?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[markros]

Well, that's all I've done so far and it's already time to get home

-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Create date: 07/28/09	
-- Description:	Returns dataset for the Route Schedule Report
-- =============================================
CREATE PROCEDURE getRouteScheduleReportData 
	-- Add the parameters for the stored procedure here
	@nCompanyID int = 1,
	@StartDate datetime = NULL, 
	@EndDate datetime = NULL
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	if @EndDate IS NULL
	 -- see [URL unfurl="true"]http://www.karaszi.com/SQLServer/info_datetime.asp#GettingRidOfTimePortion[/URL]
	   set @EndDate = DATEADD(day, DATEDIFF(day, '20040101', CURRENT_TIMESTAMP) + 1, '20040101') -- tomorrow
	
	if @StartDate is NULL
	   set @StartDate = dateadd(day,2, @EndDate) 
	
	;with cte_Hdr as 
	(SELECT [nRouteHeaderID]
      ,[nCompanyID]
      ,[cDescription]
      ,[dtRouteDate]     
      ,[dtRouteEnd]      
  FROM [dbo].[RouteHeader] where [dtRouteDate] >= @StartDate and [dtRouteDate] < @EndDate and nCompanyID = @nCompanyID)

  select Hdr.*,  from cte_Hdr HDR INNER JOIN [dbo].[RouteDetail] RD 
  ON Hdr.[nRouteHeaderID] = RD.[nRouteHeaderID] INNER JOIN [dbo].[RouteDetailCategories] RDC 
  ON RD.[nRouteDetailCategoryID] = RDC.[nRouteDetailCategoryID] LEFT JOIN [Squadware].[dbo].[Users] U
  ON RDC.cDescription = 'Users' and RD.nID = U.UserPK  


END
GO

 

[markros]

Ouch (((((((((