First Post - Dynamic Join? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

First Post - Dynamic Join?

Author

Topic

Allen42
Starting Member

10 Posts

Posted - 2009-07-22 : 13:49:49

This is my first post, and I'm relatively new to SQL, so thanks for your patience.

In MS SQL Server 2005:

I have a SOURCETABLE:
TITLE: value: "Some Title"
LINKTABLE value: "TARGETTABLE_2"
LINKKEY value: "123"
LINKCOLUMN value: "COLUMN2"

I have many possible target tables:

TARGETTABLE_1:
KEY value: "789"
COLUMN1 value: "sometext"
COLUMN2 value: "sometext 2"
COLUMN3 value: "sometext 3"
etc.

TARGETTABLE_2:
KEY value: "123"
COLUMN1 value: "sometext"
COLUMN2 value: "sometext 2"
COLUMN3 value: "sometext 3"
etc.

TARGETTABLE_nn:
etc...

My goal is to produce a view, stored procedure, or computed column, etc. that creates a results table based upon a dynamic left join from SOURCETABLE to any number of TARGETTABLES & columns:

So, for the fisrt row of SOURCETABLE, above, the results would look like:

RESULTSTABLE:
TITLE: "Some Title"
VALUECOLUMN: "sometext 2"

Issues:

I want this to be server-side, so that it can be used by various code sources. (I know how to code it in the applications easy enough, but having the table or view ready to query is much better for me in this case.)

There could NN number of target tables. The source of the data will be stored in the SOURCETABLE.

I tried creating a function that would create a dynamic (string-based) lookup via an EXEC, but alas, EXECs are not permitted in functions. My plan was to use the function in a computed column or view column.

Please point me in the right direction, and assume that I know nothing... you won't be far off.

Allen42
Starting Member

10 Posts

Posted - 2009-07-22 : 13:50:42

Great "fist" impression... that should be "first".

Never mind - figured out how to edit.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-07-22 : 15:35:57

Fist, eer I mean first (

) If can change your data structure I would highly recomend doing it now before things go really bad for you: [url]http://www.datamodel.org/NormalizationRules.html[/url]

If you are intent on using your existing design you'll have to use Dynamic SQL:
[url]http://www.sommarskog.se/dynamic_sql.html[/url]

X002548
Not Just a Number

15586 Posts

Posted - 2009-07-22 : 15:48:23

On behalf of all of the DBA's...thank you

You keep us in b'dness

Why are there "n" number of tables?

Are they created daily?

Wouldn't a single table and a datetime column be less of a headache?

HOWEVER

You could build this stuff by looking at INFORMATION_SCHEMA.Tables

I suggest you don't

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Allen42
Starting Member

10 Posts

Posted - 2009-07-22 : 16:04:32

A bit of an explanation:

(And thanks for asking questions instead of immediately going into "flame on" mode.)

SourceTable is something like a "activitylog"

Target tables are various records in the system: company, people, tickets, etc.

So I can have an activity log record that is linked to a company, and the corresponding data that I want to bring back is the company name. If it's linked to the person, I want to have "personname" available. Ticket might be "ticketnumber", etc. The common thread is the unid field that is the actual key, but means nothing to the users.

I have a medium-low grasp of normalization idealogy, which is why I'm trying to avoid storing this data in the "activitylog" record, and keep it relational.

Help!

Allen42
Starting Member

10 Posts

Posted - 2009-07-22 : 16:14:38

To further exemplify:

I have a sourcetable "ACTIVITYLOG":
TITLE: value: "Some Activity"
LINKTABLE value: "TICKETS"
LINKUNID value: "123"
LINKCOLUMN value: "TICKETNUMBER"

I have many possible target tables:

Target table 1: COMPANIES:
UNID value: "789"
COMPANYNAME value: "Acme, Inc."
ADDRESS value: "123 Main Street"
COLUMN3 value: "sometext 3"
etc.

Target table 2: TICKETS:
UNID value: "123"
TITLE value: "Problem with Printer"
TICKETNUMBER value: "ACME20091212"
COLUMN3 value: "sometext 3"
etc.

TARGETTABLE_nn:
etc...

X002548
Not Just a Number

15586 Posts

Posted - 2009-07-22 : 16:16:20

You actually kinda have an RI Model...you just need the parent tables for each type

The a partitioned view, and I thing you're set

however...you could make it all 1 table with a type column

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Allen42
Starting Member

10 Posts

Posted - 2009-07-22 : 16:21:37

quote:
Originally posted by X002548

You actually kinda have an RI Model...you just need the parent tables for each type

The a partitioned view, and I thing you're set

however...you could make it all 1 table with a type column

huh? Can you give me some research links or something? I don't even play a DBA on TV, and there's no Holiday Inn Express near here.

Allen42
Starting Member

10 Posts

Posted - 2009-07-22 : 16:26:11

Oh... the target tables are part of an existing system. I could add columns (within reason), but cannot eliminate any.

The activitylog is the new functionality.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-07-22 : 16:34:43

how many possible target tables? if only a very few (and this is going to get ugly) u can do a UNION


SELECT	<field list>
FROM	sourceTable s
JOIN	targetTable1 t1
On	t1.keyCol = s.keyCol
UNION ALL
SELECT	<field list>
FROM	sourceTable s
JOIN	targetTable2 t2
On	t2.keyCol = s.keyCol
UNION ALL
SELECT	<field list>
FROM	sourceTable s
JOIN	targetTable1 t3
On	t3.keyCol = s.keyCol

Of coure that will turn into a performance and maintenance nightmare.

what is the output supposed to look like?

Allen42
Starting Member

10 Posts

Posted - 2009-07-22 : 17:01:17

Depending on the customer's implementation, they could have anywhere from 3 to 12 different target tables.

I would want the output to be something like:

DESIRED RESULTS:

Row #1:
TITLE: "Some Activity" (TITLE field from ACTIVITYLOG)
LINKEDTO: "TICKETS" (LINKTABLE field)
LINKVALUE: "ACME20091212" (TICKETNUMBER field from TICKETS table)

Row #1:
TITLE: "Invoiced Acme" (TITLE field from ACTIVITYLOG)
LINKEDTO: "COMPANIES" (LINKTABLE field)
LINKVALUE: "Acme, Inc." (COMPANYNAME field from COMPANIES table)

Allen42
Starting Member

10 Posts

Posted - 2009-07-23 : 14:04:14

So no can do?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-07-23 : 14:42:29

is it a KNOWN # of target tables?

if so, sure...just gotta write the t-sql.

sounds like the source table already has it though...

Allen42
Starting Member

10 Posts

Posted - 2009-07-23 : 14:50:37

No, as I said, "they could have anywhere from 3 to 12 different target tables". The customer may not have implemented our ticket module, for example, so there would be no ticket table. They may have a custom table as well. In this case, it's truly dynamic.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-07-23 : 14:52:02

quote:
Originally posted by Allen42

So no can do?

Sure, dynamic SQL..

You *might* be able to do it with a bunch of unions. But, since each table is going to have different numbers of columns and data types; it'll probably get so unwieldy that it's not worth it. Unfortunately, I couldn't say for sure without knowing all the info.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-07-23 : 14:52:44

why not just pull from the source table using the same logic that populates the target tables?

Allen42
Starting Member

10 Posts

Posted - 2009-07-23 : 14:58:43

The target tables are populated by user input.
I guess I'll try a SP with dynamic SQL.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources