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 

Date: Mon, 23 Aug 2004 14:19:34 -0400

"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.

Relevant Pages

  • Re: Join to one of two tables (based on a value in the source table)
    ... >> yourself a query that returns apples or octopi. ... > or the database in which it resides). ... > table to store similar information, for items not found in that lookup ... I need data from a table that doesn't belong to me ...
    (microsoft.public.sqlserver.programming)
  • Re: Row cannot be located for updating error (ADO)
    ... "DBEdit1 the field double clicked) in the OnDoubleClick event is writing ... directly to the database but the data aware controls are writing to the ... >>lookup value) dependant on the users selection this is then saved to the ... > I assume your query is a Join using fields that come from the table your ...
    (borland.public.delphi.database.ado)
  • Forms for user to create own query?
    ... Has anyone developed a generic set of formwhere a user is empowered to ... query on certain fields within a small database (like joining a couple of ...
    (microsoft.public.access.formscoding)
  • RE: change field properties, reflect in query
    ... it is likely you are doing something wrong with your database design. ... what you are looking up with the lookup lists, ... > created multitable forms using a query that contains both main tables. ...
    (microsoft.public.access.gettingstarted)
  • Re: transaction disabling
    ... All this is internal to the database. ... couple of fields to the new/changed lookup key values. ... I am doing this as I have a dynamic query building form that users ... The filter keys are higher in the ...
    (microsoft.public.sqlserver.server)