Prev: Where to find AdventureWorks database samples
Next: modeling a contract and effective dates
From: Hao on 17 Mar 2010 08:44
Hi

I am working on an existing database that has three tables, no option
to change the database structure, but have to work on it.

CREATE TABLE Building (
b_id INT PRIMARY KEY,
b_name char(50));

CREATE TABLE Door(
d_id INT PRIMARY KEY,
d_name char(30),
d_key char(10),
d_BuildingID int references Building(b_id));

CREATE TABLE History (
h_key char(40) PRIMARY KEY,
transaction_time DATETIME PRIMARY KEY);

The h_hey in the History is actually d_id + "-" + d_key, now I need to
join three tables, get the building, door and associated last
transaction records. I will have to nest join to the h_key value from
Door table, which is in form of d_id + "-" + d_key, then use it get
the most recent history record from History table.

Any hints or suggestions will be highly appreciated.

Thank you
From: Plamen Ratchev on 17 Mar 2010 11:30
It will be best to normalize the key (use computed columns or a view to split the two attributes that make the key in
History, then add indexes). That will make querying easy and more efficient.

With your current schema you can do this:

SELECT b_name, d_name, transaction_time
FROM (
SELECT b_name, d_name, transaction_time,
ROW_NUMBER() OVER(PARTITION BY H.h_key ORDER BY H.transaction_time DESC) AS rk
FROM History AS H
JOIN Door AS D
ON H.h_key = CAST(CAST(D. d_id AS VARCHAR(10)) + '-' + D.d_key AS CHAR(40))
JOIN Building AS B
ON D.d_BuildingID = B.b_id) AS T
WHERE rk = 1;

--
Plamen Ratchev
http://www.SQLStudio.com
 | 
Pages: 1
Prev: Where to find AdventureWorks database samples
Next: modeling a contract and effective dates