How can I do this??  <TableA.tableName> is my psuedo for the TABLE I want to
JOIN to but I can't figure out how


    SELECT    TableA.tableName,  <TableA.tableName>.someData
    FROM        TableA
                                 LEFT OUTER JOIN
                                        <TableA.tableName>
                                 ON
                                        TableA.foriegnKey =
<TableA.tableName> .primaryKey

    WHERE     TableA.tableName = 'TableB'

See below for TableA and TableB scripts.   I am using SQL Server 2000

Thanks

JIM

==============

Table A contains a value, a string field containing a table name and a
foriegn key into that Table::

BEGIN TRANSACTION
CREATE TABLE dbo.TableA
(
primayKey int NOT NULL IDENTITY (1, 1),
someValue varchar(25) NULL,
tableName varchar(50) NULL,
foriegnKey int NULL
)  ON [PRIMARY]
GO
ALTER TABLE dbo.TableA ADD CONSTRAINT
PK_TableA PRIMARY KEY CLUSTERED
(
primayKey
) ON [PRIMARY]

GO
COMMIT
GO

Table B contains some data, and a primary key::  ( assume there are many
tables like Table B jujst with different names )

BEGIN TRANSACTION
CREATE TABLE dbo.TableB
(
primaryKey int NOT NULL IDENTITY (1, 1),
someData varchar(50) NULL
)  ON [PRIMARY]
GO
ALTER TABLE dbo.TableB ADD CONSTRAINT
PK_TableB PRIMARY KEY CLUSTERED
(
primaryKey
) ON [PRIMARY]

GO
COMMIT
GO

james (nospam@hypercon.net) writes:
You will have to use dynamic SQL for this. See my article on
http://www.sommarskog.se/dynamic_sql.html for introduction on the topic.

I don't know your business domain at all, and you may have valid reasons
for your design. However, it is quite an odd way to use a relational
database, and there may be a better way to skin the cat.

Erland,

I would love a better way to skin the cat, but do not know how. Basically, I
want to allow users to be able to add user defined data fields to their
tables without pre-limiting them to how many thay can have or what they can
be called.  So, I have a table that holds their custom data, and a reference
to the record they want to attach that data to.  Only problem is that I also
need to store the Table Name that that reference points to.  That is the
only way i can allow this for every tablein the database without limits and
without pre knowledge of anything about the tables or the data.  If I can
make it work, it will be pretty darn cool.

If yhou have the solution, please inform me,

Thanks,

JIM


Show quoteHide quote

james (nospam@hypercon.net) writes:
I still don't know enough to say for sure. Where do the base tables come
from? Are they defined by your application? How many tables do you have?
Is it really meaningful to attach custom-columns to any data?

Rather than having one table with custom data, I would have one per table
where I would like to support this. Such a table would have the PK of
its main table, with a "usercol" key added to it. That is the user fields
would be rows in that table, not columns. Probably would also need a
definition table to determine which user fields that are available for
a table. Possibly this could be put into a central table.

If you put all those user fields into a single table, you will be for
a painful exercise of dynamic SQL.

Dear Ghod, Lord in Heaven!!!  WHY??  Are you dumber than any random
future user of the system?  If you are that dumb, why are you coding
anything?  Please, please, please read at least oen book on RDBMS
before you kill someone.

You have no idea what data and meta-data are, you have no data model,
you do not know that a field and coumn are **totally** different
concepts. etc.  if I put your stuff in a book, people would think I was
making it up.  You are that bad an SQL programmer.

I think what Celko is trying to say, is that you will find it impossible to
make this work relationally without writing ten (100?) times the amount of
code it would take to simply add the fields when the users need them, or
spend the extra time on requirements and put the fields in now.

Granted, not all environments allow for properly defining your structures up
front, and sometimes you are stuck making modifications as you go.
Nonetheless, you should avoid this sort of thing if at all possible.  If you
absolutely have to go this route, take a look at Erlands approach, it is a
fair bit simpler than what you are suggesting.

Or just throw any use defined fields into XML and place a spot for xml in
each table.  You won't be able to query on the individual values, btu
everything will be stored in one place and a lot simpler to code.

fields [sic] to their tables without pre-limiting them to how many thay can
have or what they can be called. <<
Show quoteHide quote

Joe,

you forgot to mention that my parents must be first cousins and that my
family must have descended from apes and ignoamuses.

I would love a better solution to this, because what I have has grown into a
monster from somthing fairly simple.

Basically the situation is this.  My users asked if they could have a way to
record bits of information and tag them to their Customers.  Kind of like
Notes only not just notes but other things too.  So, I gave them a Table for
this with a basic 1 to many relationship.  But, then they said gee, wouldn't
that be nice if we caould do the same thing to for our Order Items oh, and
also our Vendors and then I realized this would need a better design so that
it could be re-used so instead of creating a many table for all these Master
tables I just created one table, and let them put all their Custom bits of
data in that table.  This works just fine.  Now they Love the feature so
much they want the ability, if they should so choose, to add these items to
Any table in the entire database.  Here is where I stopped.  I thought hmm,
maybe a dynamic query with the Master Table name in it would allow me to
filter to the custom data for for any table.  So, I asked the question.  I
haven't gone past that.  Yes, I agree some questions can appear to be
stupid, but that is usually when you don't slow down, count to 10, and then
ask for more detail.  I do that a lot too, it's my ADD.  Have you been
checked for that?  It is quite common for very intelligent people to be a
bit ADD, we tend to speak and not listen.

Ok, now let me have it  ;-)

By the way, I own and have read your SQL for smarties, and your Trees and
Hierachies books.  Vey nice indeed.

Thanks,

JIM


Show quoteHide quote

OBJECTPROPERTY problem
T-SQL: How to loop through the resultset from another procedure?
optimize queries with unexpected results
"Where date" problem
Help with Stored Prcoedure