design help

[kamran26]

i m trying to solve a problem for last couple of days and would appreciate any help...

i have a transction table...in which i record transactions from one location to another location...

example

TransID FromLocationID ToLocationID

there are other fields too but i m mentioning this important one.

There are two types of Locations one is hospital and the other is warehouse. Details of each is kept in their respective tables..

The FRomLocationID, ToLocationID comes from the following table

LocationID, LocationType (hospital or warehouse) ,ActualID

in the above i would assign a unique LocationID but the ActualID would either come from the hospital table or from the warehouse table...The problem is that how can i join it from the transaction table..like if the location if of type hospital then i get the value from hospital table..and vice versa...I have to create a view for all this..also i have to create reports..

thanx

[Rawhide]

Select ....
From Transactions T
Inner Join Locations LFrom On LFrom.LocationID = T.FromLocationID
Inner Join Hospitals HFrom On HFrom.HospitalID = LFrom.ActualID And LFrom.LocationType = 'hospital'
Inner Join Locations LTo On LTo.LocationID = T.ToLocationID
Inner Join Hospitals HTo On HTo.HospitalID = LTo.ActualID And LTo.LocationType = 'hospital'
Union
Select ....
From Transactions T
Inner Join Locations LFrom On LFrom.LocationID = T.FromLocationID
Inner Join Warehouses HFrom On HFrom.WarehouseID = LFrom.ActualID And LFrom.LocationType = 'warehouse'
Inner Join Locations LTo On LTo.LocationID = T.ToLocationID
Inner Join Warehouses HTo On HTo.WarehouseID = LTo.ActualID And LTo.LocationType = 'warehouse'

[kamran26]

thanx for the reply..i appreciate your help..i would try to consume this..and if have problem will post the message.

[kamran26]

i just want to ask can this be done to create a view because you canot use union in a view and i must create a view so that the vb application can just extracts the data and present it to the user

[Rawhide]

You can use a union in a view.