Re: Join to one of two tables (based on a value in the source table)
From: Scott Lyon (scott.RED.lyon_at_WHITE.rapistan.BLUE.com)
Date: 08/23/04
- Next message: rob: "Appropriate newsgroup for Reporting services questions"
- Previous message: Adam Machanic: "Re: case clause"
- In reply to: Scott Lyon: "Re: Join to one of two tables (based on a value in the source table)"
- Next in thread: Hugo Kornelis: "Re: Join to one of two tables (based on a value in the source table)"
- Reply: Hugo Kornelis: "Re: Join to one of two tables (based on a value in the source table)"
- Reply: Joe Celko: "Re: Join to one of two tables (based on a value in the source table)"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 23 Aug 2004 14:23:59 -0400
"Scott Lyon" <scott.RED.lyon@WHITE.rapistan.BLUE.com> wrote in message
news:OOv7J3TiEHA.2448@TK2MSFTNGP12.phx.gbl...
>
> "Joe Celko" <jcelko212@earthlink.net> wrote in message
> news:u2L3zaSiEHA.2848@TK2MSFTNGP10.phx.gbl...
> > >> .. bit .. uniqueidentifier .. table1 and table2 do not have the same
> > structure .. fields .. I want to do a select from tableSource, that will
> > join to table1 or table2 .. depending on whether chooseTable is 0 or 1
> > . VARCHAR(50). <<
> >
> > Okay, you want to use an assembly language level bit construct to give
> > yourself a query that returns apples or octopi.
> >
> > Have you asked yourself just what this kind of query would mean in a
> > data model? If it is meaningful, then this attribute is split over two
> > tables and you have some serious design problems. If it is not
> > meaningful, then you have some serious programming problems.
> >
> > Since you mention having a uniqueidentifier and VARCHAR(50) columns,
> > this looks like an ACCESS port in which no redesign work was done.
> > While it is possible that something is really VARCHAR(50), 80-90% of the
> > time I find this is the quickie kludge of a newbie, so my warnign light
> > goes on.
> >
> > What are you trying to do? You have told us HOW you want to do it.
> > Where is the DDL?
> >
> > --CELKO--
> > ===========================
> > Please post DDL, so that people do not have to guess what the keys,
> > constraints, Declarative Referential Integrity, datatypes, etc. in your
> > schema are.
> >
> > *** Sent via Developersdex http://www.developersdex.com ***
> > Don't just participate in USENET...get rewarded for it!
>
>
> In a nutshell, what I need to do is create a stored procedure or view that
> will return data from one table, and then joined to another table (which
> table it joins to is dependant on the bit I mentioned in the first table).
>
> The reason, is there is a lookup table that's in another database, to
which
> I have read-access (in other words, I do not have ownership of that table,
> or the database in which it resides). In my database, I need to have my
own
> table to store similar information, for items not found in that lookup
> table.
>
> Then I need to be able to call a stored procedure or view to see the data,
> so it won't look like it's coming from two tables, but rather from one
> source.
>
>
> Let me throw together a better example (sticking to just one database, to
> simplify things a little), to explain this.
>
> Here's the table structure:
>
>
> CREATE TABLE [dbo].[tableLists] (
> [ListID] [int] IDENTITY (1, 1) NOT NULL ,
> [UnitName] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [chooseTable] [bit] NOT NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[tableLists_lookup1] (
> [UnitName] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [UnitDescription] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [UnitWeight] [float] NULL ,
> [UnitLength] [float] NULL ,
> [UnitPrice] [money] NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[tableLists_lookup2] (
> [UNITNM] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [UNITDSC] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [UNITPRC] [money] NULL
> ) ON [PRIMARY]
> GO
>
>
>
> For this example, tableLists is what is driving my query, and
> tableLists_lookup1 is the table that I own that I want to join to (if the
> chooseTable bit is set to 0), joining tableLists.UnitName to
> tblLists_lookup1.UnitName. The tableLists_lookup2 table is the one that I
do
> not own, and that I want to join to if the chooseTable (from tableLists)
bit
> is set to 1, joining tableLists.UnitName to tblLists_lookup2.UNITNM.
>
> The thing is, tblLists_lookup1.UnitDescription is the same field
(basically)
> as tblLists_lookup2.UNITDSC. I want to return one value in my query (call
it
> UnitDescription, for example), that will be populated by either lookup1 or
> lookup2 based on the chooseTable value.
>
>
> Same goes for lookup1.UnitPrice and lookup2.UNITPRC
>
>
> So as you see, it's not so much a design issue as much as a situation
where
> I have data in two locations, and I want to be able to choose from which
> location that data is retrieve.
Don't misunderstand me... If there is a better way to do this, I'm open to
suggestions. However, I am restricted to the facts that:
1) I need data from a table that doesn't belong to me
2) For my application to be able to add data, I need to add that data to a
table that DOES belong to me
3) It needs to seemlessly return data from both of those tables as if they
were one source.
- Next message: rob: "Appropriate newsgroup for Reporting services questions"
- Previous message: Adam Machanic: "Re: case clause"
- In reply to: Scott Lyon: "Re: Join to one of two tables (based on a value in the source table)"
- Next in thread: Hugo Kornelis: "Re: Join to one of two tables (based on a value in the source table)"
- Reply: Hugo Kornelis: "Re: Join to one of two tables (based on a value in the source table)"
- Reply: Joe Celko: "Re: Join to one of two tables (based on a value in the source table)"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
