Database

CREATE FUNCTION GetProjectIDs (@nClientID int)  
RETURNS  varchar(1000) AS  
BEGIN 

DECLARE @nProjectID int
DECLARE @cProjectIDs varchar(50)

SELECT @cProjectIDs = ''

DECLARE  ProjectIDs CURSOR FOR 
SELECT ProjectID 
FROM ProjectClient
WHERE ClientID = @nClientID
ORDER BY ProjectID

OPEN ProjectIDs
FETCH NEXT FROM ProjectIDs INTO @nProjectID 
WHILE @@FETCH_STATUS = 0
BEGIN
	select @cProjectIDs = @cProjectIDs + case when len(@cProjectIDs) = 0 then '' else ',' end + cast( @nProjectID as varchar)
	FETCH NEXT FROM ProjectIDs INTO @nProjectID 
END

CLOSE ProjectIDs
DEALLOCATE ProjectIDs
RETURN(@cProjectIDs)
END

select *, dbo.GetProjectIDs(client.clientid) as ProjectIDs from client

CREATE PROC uspGetClientsProjects
AS
SELECT ClientID, Field1, Field2
FROM Client

SELECT ClientID, ProjectID
FROM ProjectClient

Rob Graham wrote:

The JobsHeld table has 0-n rows for each row in the JobSeekers table

<font>JobSeekers

Jsid | lastname | firstname | Address 1  | address 2 | Telephone
---------------------------------------------------------------
1    | Smith    | Robert    | 123 22nd St| Apt 35    | 555-9898
----------------------------------------------------------------
2    | Jones    | Bill      | 1532 1st St| <null>    | 555-9987
-----------------------------------------------------------------
3    | Green    | James     | 12 Maple St| <null>    | 555-8906

JobHistory

JSID | JobNum   | Start     | End        | Employer
------------------------------------------------------------
1    | 1        | May 1985  | June 1986  | Acme tools
------------------------------------------------------------
1    | 2        | July 1986 | March 2006 | Big Gun Software
------------------------------------------------------------
2    | 1        | march 2000| March 2006 | Acme Componets

</null></null></font>

Judah Himango wrote:

- not model the tables after the real objects?

Judah Himango wrote:

- create a view that returns all possible data for all 15 or so tables?

Judah Himango wrote:

- query the FooTable, then when I get the results back, do another query on the table specific for that type?

EricDV wrote:

Judah Himango wrote:
- create a view that returns all possible data for all 15 or so tables?

I tend to not like this kind of stuff. It usually looks like a mess, is not easily scaleable, and is difficult to maintain. You have to remember to change the view whenever you add a new FooInheritor type.

EricDV wrote:

This is probably easiest to write, along with keeping things normalized. But, two queries aren't as fun as one.

FooRow[] rows = fooTable.GetResults(); // Query #1!
for(int i = 0; i < rows.Length; i++)
{
   Foo theFoo;
   if(rows[i].Type == Foo)
   {
      theFoo = new Foo(rows[i]);
   }
   else if(rows[i].Type == FooInheritor)
   {
      theFoo = QueryFooInheritorTable(rows[i].ID); // Another query!!
   }
   else if(rows[i].Type == AnotherFooInheritor)
   {
      theFoo = QueryAnotherFooInheritorTable(rows[i].ID); // Another query!!
   }
   ... // and so on
}

create procedure GetFooInfo
as 
DECLARE @nFooID int,@cType varchar(50),@cSQL varchar(2000)

set @cSQL = ''

DECLARE  fooBase CURSOR FOR 
SELECT distinct Type
FROM fooTable

OPEN fooBase 
FETCH NEXT FROM fooBase INTO @cType 
WHILE @@FETCH_STATUS = 0
BEGIN
	if len(@cType) > 0
	     set @cSQL = @cSQL + ' LEFT JOIN ' + @cType + ' ON fooTable.FooID = ' +  @cType + '.FooInheritorID '
      	FETCH NEXT FROM fooBase INTO @cType 

END

CLOSE fooBase 
DEALLOCATE fooBase 
exec('SELECT * FROM fooTable ' + @cSQL)
go

CREATE TABLE fooTable (
	FooID int IDENTITY (1, 1) NOT NULL,
	Type varchar (50),
	FooBaseInfo varchar(50) 
) ON [PRIMARY]
GO

insert into fooTable (Type,FooBaseInfo) values ('','this is a basic foo')
insert into fooTable (Type,FooBaseInfo) values ('FooInheritor1','FooInheritor1 foo 1')
insert into fooTable (Type,FooBaseInfo) values ('FooInheritor2','FooInheritor2 foo 1')
insert into fooTable (Type,FooBaseInfo) values ('FooInheritor3','FooInheritor3 foo 1')
insert into fooTable (Type,FooBaseInfo) values ('FooInheritor4','FooInheritor4 foo 1')
insert into fooTable (Type,FooBaseInfo) values ('FooInheritor2','FooInheritor2 foo 2')
insert into fooTable (Type,FooBaseInfo) values ('FooInheritor4','FooInheritor4 foo 2')
insert into fooTable (Type,FooBaseInfo) values ('FooInheritor4','FooInheritor4 foo 3')
insert into fooTable (Type,FooBaseInfo) values ('FooInheritor2','FooInheritor2 foo 3')
insert into fooTable (Type,FooBaseInfo) values ('FooInheritor4','FooInheritor4 foo 4')

CREATE TABLE FooInheritor1 (
	FooInheritorID int,
	Foo1Specific varchar(50)  
) ON [PRIMARY]
INSERT INTO FooInheritor1 select FooID,'Specific to 1' as Specific FROM fooTable where Type = 'FooInheritor1'

CREATE TABLE FooInheritor2 (
	FooInheritorID int,
	Foo2Specific varchar(50)  
) ON [PRIMARY]
INSERT INTO FooInheritor2 select FooID,'Specific to 2' as Specific FROM fooTable where Type = 'FooInheritor2'

CREATE TABLE FooInheritor3 (
	FooInheritorID int,
	Foo3Specific varchar(50)  
) ON [PRIMARY]
INSERT INTO FooInheritor3 select FooID,'Specific to 3' as Specific FROM fooTable where Type = 'FooInheritor3'


CREATE TABLE FooInheritor4 (
	FooInheritorID int,
	Foo4Specific varchar(50) 
) ON [PRIMARY]
INSERT INTO FooInheritor4 select FooID,'Specific to 4' as Specific FROM fooTable where Type = 'FooInheritor4'
GO

FooTypes[] fTypes = fooTable.Query("SELECT DISTINCT FooInheritorType FROM fooTable"); // Query #1
for(int i = 0; i < fTypes.Length; i++)
{
   string cQuery;
   if fTypes[i] == "")
      cQuery = "SELECT * FROM fooTable ft WHERE FooInheritorType = ''"; //for base classes that are not Inheritors
   else
      cQuery = "SELECT * FROM fooTable ft INNER JOIN " + fTypes[i] + " fi ON  ft.id = fi.id"; 

   FooRow[] rows = fooTable.Query(cQuery); // Query 2,3,4,... - for as many types
   for(int i2 = 0; i2 < rows.Length; i2++)
   {
      Foo theFoo = new Foo(rows[i2]);

      ...//other processing
   }
}