Conditional Join

Hello,
I have the following problem...
I am musing ADO.NET with SQL Server. I have a table that stores events
that are linked to different tables.. for example, lets suppose that I
have the following tables : Airplanes, Cars, Trains. Each table is
different, however, events produced by any of these are stored on a
single Event Table with the following fields:
EventID, OwnerTable, OwnerID, EventTime, Description.
Where OwnerTable might be Airplanes, Cars or Trains and OwnerID is the
unique ID within the given table. I now want to create a SELECT
statement that shows all events and joins the particular row to the
correct record based on the OwnerTable and OwnerID to show common fields
among all. For example, assume that all three tables have a COLOR
field, the resulting query should produce the following:
Event Table
Event ID: 1
OwnerTable: Airplanes
OwnerID: 1
Color : Red ( This is the COLOR field on the Airplanes table with
AirplaneID = 1)
Event Table
Event ID: 2
OwnerTable: Trains
OwnerID: 1
Color : Blue ( This is the COLOR field on the Trains table with
TrainID = 1)
Thanks,
Jeronimo BertranHi J,
As per the info you have porvided I have created tables and inserted the
data.
The query you require is at the end.
Create table plane (objectID Int Primary Key Identity,Color Varchar(1000))
go
Create table car (objectID Int Primary Key Identity,Color Varchar(1000))
go
Create table train (objectID Int Primary Key Identity,Color Varchar(1000))
go
Create table Events (EventID Int Primary Key Identity, OwnerTable
Varchar(1000),
OwnerID Int, EventTime DateTime, Description Varchar(1000))
go
Insert Into plane values ('Blue')
go
Insert Into plane values ('Blue1')
go
Insert Into plane values ('Blue2')
go
Insert Into car values ('Red')
go
Insert Into car values ('Red1')
go
Insert Into car values ('Red2')
go
Insert Into train values ('Yellow')
go
Insert Into train values ('Yellow1')
go
Insert Into train values ('Yellow2')
go
Insert into Events Select 'Plane',ObjectID,GetDate(),'Planes has added this'
from plane
go
Insert into Events
Select 'Train',ObjectID,GetDate(),'Trains has added this' from train
Union
Select 'Car',ObjectID,GetDate(),'Cars has added this' from Car
go
--This is your query
Select Events.* , Case Events.OwnerTable
When 'Plane' Then Plane.Color
When 'Train' Then Train.Color
When 'Car' Then Car.Color
Else '{Blank}'
End
From Events Left Outer Join Plane on Events.OwnerId = Plane.ObjectID And
Events.OwnerTable = 'Plane'
Left Outer Join Train on Events.OwnerId = Train.ObjectID And
Events.OwnerTable = 'Train'
Left Outer Join Car on Events.OwnerId = Car.ObjectID And Events.OwnerTable
= 'Car'
Please respond if it solves your problem
Thanks,
Vishal Khajuria
Sungard SCT India
"Jeronimo Bertran" wrote:

> Hello,
> I have the following problem...
> I am musing ADO.NET with SQL Server. I have a table that stores events
> that are linked to different tables.. for example, lets suppose that I
> have the following tables : Airplanes, Cars, Trains. Each table is
> different, however, events produced by any of these are stored on a
> single Event Table with the following fields:
> EventID, OwnerTable, OwnerID, EventTime, Description.
> Where OwnerTable might be Airplanes, Cars or Trains and OwnerID is the
> unique ID within the given table. I now want to create a SELECT
> statement that shows all events and joins the particular row to the
> correct record based on the OwnerTable and OwnerID to show common fields
> among all. For example, assume that all three tables have a COLOR
> field, the resulting query should produce the following:
> Event Table
> Event ID: 1
> OwnerTable: Airplanes
> OwnerID: 1
> Color : Red ( This is the COLOR field on the Airplanes table with
> AirplaneID = 1)
> Event Table
> Event ID: 2
> OwnerTable: Trains
> OwnerID: 1
> Color : Blue ( This is the COLOR field on the Trains table with
> TrainID = 1)
> Thanks,
> Jeronimo Bertran
>|||Hi Jeronimo Bertran,
As per the info you have provided, I have created table and populated them
with data. The query you are looking for is at end. I am giving you all the
scripts so that there is no misinterpertation.
Create table plane (objectID Int Primary Key Identity,Color Varchar(1000))
GO
Create table car (objectID Int Primary Key Identity,Color Varchar(1000))
GO
Create table train (objectID Int Primary Key Identity,Color Varchar(1000))
GO
Create table Events (EventID Int Primary Key Identity, OwnerTable
Varchar(1000), OwnerID Int, EventTime DateTime, Description Varchar(1000))
GO
Insert Into plane values ('Blue')
GO
Insert Into plane values ('Blue1')
GO
Insert Into plane values ('Blue2')
GO
Insert Into car values ('Red')
GO
Insert Into car values ('Red1')
GO
Insert Into car values ('Red2')
GO
Insert Into train values ('Yellow')
GO
Insert Into train values ('Yellow1')
GO
Insert Into train values ('Yellow2')
GO
Insert into Events Select 'Plane',ObjectID,GetDate(),'Planes has added this'
from plane
GO
Insert into Events
Select 'Train',ObjectID,GetDate(),'Trains has added this' from train
Union
Select 'Car',ObjectID,GetDate(),'Cars has added this' from Car
GO
--This is the query you are lookin for
Select Events.* , Case Events.OwnerTable
When 'Plane' Then Plane.Color
When 'Train' Then Train.Color
When 'Car' Then Car.Color
Else '{Blank}'
End
From Events Left Outer Join Plane on Events.OwnerId = Plane.ObjectID And
Events.OwnerTable = 'Plane'
Left Outer Join Train on Events.OwnerId = Train.ObjectID And
Events.OwnerTable = 'Train'
Left Outer Join Car on Events.OwnerId = Car.ObjectID And Events.OwnerTable
= 'Car'
Please respond if it solves your problem.
Regard ,
Vishal Khajuria
Sungard SCT India
"Jeronimo Bertran" wrote:

> Hello,
> I have the following problem...
> I am musing ADO.NET with SQL Server. I have a table that stores events
> that are linked to different tables.. for example, lets suppose that I
> have the following tables : Airplanes, Cars, Trains. Each table is
> different, however, events produced by any of these are stored on a
> single Event Table with the following fields:
> EventID, OwnerTable, OwnerID, EventTime, Description.
> Where OwnerTable might be Airplanes, Cars or Trains and OwnerID is the
> unique ID within the given table. I now want to create a SELECT
> statement that shows all events and joins the particular row to the
> correct record based on the OwnerTable and OwnerID to show common fields
> among all. For example, assume that all three tables have a COLOR
> field, the resulting query should produce the following:
> Event Table
> Event ID: 1
> OwnerTable: Airplanes
> OwnerID: 1
> Color : Red ( This is the COLOR field on the Airplanes table with
> AirplaneID = 1)
> Event Table
> Event ID: 2
> OwnerTable: Trains
> OwnerID: 1
> Color : Blue ( This is the COLOR field on the Trains table with
> TrainID = 1)
> Thanks,
> Jeronimo Bertran
>|||Jeronimo
SELECT <column lists> FROM Airplanes A JOIN Events E
ON A.EventId=E.EventId AND A.OwnerID=E.OwnerID
If it does not help please post DDL+ sample data + expected result
"Jeronimo Bertran" <jeronimo.bertran@.newsgroup.nospam> wrote in message
news:eexdtw7FFHA.1476@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I have the following problem...
> I am musing ADO.NET with SQL Server. I have a table that stores events
> that are linked to different tables.. for example, lets suppose that I
> have the following tables : Airplanes, Cars, Trains. Each table is
> different, however, events produced by any of these are stored on a
> single Event Table with the following fields:
> EventID, OwnerTable, OwnerID, EventTime, Description.
> Where OwnerTable might be Airplanes, Cars or Trains and OwnerID is the
> unique ID within the given table. I now want to create a SELECT
> statement that shows all events and joins the particular row to the
> correct record based on the OwnerTable and OwnerID to show common fields
> among all. For example, assume that all three tables have a COLOR
> field, the resulting query should produce the following:
> Event Table
> Event ID: 1
> OwnerTable: Airplanes
> OwnerID: 1
> Color : Red ( This is the COLOR field on the Airplanes table with
> AirplaneID = 1)
> Event Table
> Event ID: 2
> OwnerTable: Trains
> OwnerID: 1
> Color : Blue ( This is the COLOR field on the Trains table with
> TrainID = 1)
> Thanks,
> Jeronimo Bertran|||Try this:
SELECT E.eventid, E.ownertable, E.ownerid,
COALESCE(A.color, T.color) AS color
FROM Events AS E
LEFT JOIN AirPlanes AS A
ON E.ownerid = A.airplaneid
AND E.ownertable = 'Airplanes'
LEFT JOIN Trains AS T
ON E.ownerid = T.trainid
AND E.ownertable = 'Trains'
Alternatively, why not put those common columns in a single table
across all the types of transport and in the separate tables just have
columns that are specific to that subtype - that would greatly simplify
this type of query.
If Event is some kind of system-maintained audit trail then its maybe
reasonable to hold the applicable table name in there but otherwise I
would suggest that the table name is probably a poor way to identify
the entity. Don't you have identifying codes for the types "Airplanes",
"Trains", etc? In general its best not to mix data and metadata in a
table - doing so suggests that there may be something missing from the
data model.
David Portas
SQL Server MVP
--|||Thanks David,
The COALESCE will do the job. Yes I do have identifying codes for the
different types whcih I can use instead of the table name.|||Thanks Vishal,
Especially for being so thorough. Yor implementation solved my problem.