Record (entity) versions...

From: sz Tomi <sztomi_at_seznam.cz>
Date: 9 Jul 2004 14:44:28 -0700
Message-ID: <e2be8eb1.0407091344.65732f89_at_posting.google.com>

Database newbie question:

My sample database (T-SQL syntax):

CREATE DATABASE sample
GO
USE sample
CREATE TABLE customers (CustomerId int IDENTITY(1,1) PRIMARY KEY CLUSTERED,name varchar(50),address varchar(50)) CREATE TABLE invoices (InvoiceId int IDENTITY(1,1) PRIMARY KEY CLUSTERED,date datetime,CustomerId int REFERENCES customers(CustomerId))
INSERT customers (name,address) VALUES ('First Company Ltd.','New York')
INSERT customers (name,address) VALUES ('Second Company Ltd.','Washington')

INSERT invoices (date,CustomerId) VALUES ('Jan 1, 2004',1)
INSERT invoices (date,CustomerId) VALUES ('Jan 2, 2004',1)
INSERT invoices (date,CustomerId) VALUES ('Jan 2, 2004',2)

SELECT * FROM customers
GO

CustomerId name address

1		First Company Ltd.		New York
2		Second Company Ltd.	Washington

SELECT i.date,c.name,c.address FROM invoices AS i INNER JOIN customers AS c ON i.CustomerId=c.CustomerId
GO

date name address

2004-01-01 00:00:00.000		First Company Ltd.		New York
2004-01-02 00:00:00.000		First Company Ltd.		New York
2004-01-02 00:00:00.000		Second Company Ltd.	Washington

UPDATE customers SET address='Boston' WHERE name='First Company Ltd.' GO
INSERT invoices (date,CustomerId) VALUES ('Jan 3, 2004',1) SELECT * FROM customers
GO

CustomerId name address

1		First Company Ltd.		Boston
2		Second Company Ltd.	Washington

SELECT i.date,c.name,c.address FROM invoices AS i INNER JOIN customers AS c ON i.CustomerId=c.CustomerId
GO

date name address

2004-01-01 00:00:00.000		First Company Ltd.		Boston
2004-01-02 00:00:00.000		First Company Ltd.		Boston
2004-01-02 00:00:00.000		Second Company Ltd.	Washington
2004-01-03 00:00:00.000		Second Company Ltd.	Boston

Is it possible in any of the RDBMS's to make this last query return the following result set?

2004-01-01 00:00:00.000		First Company Ltd.		New York
2004-01-02 00:00:00.000		First Company Ltd.		New York
2004-01-02 00:00:00.000		Second Company Ltd.	Washington
2004-01-03 00:00:00.000		First Company Ltd.		Boston

Received on Fri Jul 09 2004 - 23:44:28 CEST