Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004
    Location
    Slovakia
    Posts
    32

    Record (entity) versions...

    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
    (sz)Tomi

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    What, you mean that the select should ignore what is in the database and print what used to be there but isn't now? No.

    What you can do is keep history. For example:

    create table customer_address (customerid references customer, from_date date, to_date date, address varchar2(50),
    primary key (customerid, from_date);

    insert into customer_address values (1, '2004-01-01','2004-01-02','New York');
    insert into customer_address values (1, '2004-01-03','2999-01-01','Boston');

    Then use the dates in the join:

    SELECT i.date,c.name,c.address FROM invoices AS i INNER JOIN customers AS c ON i.CustomerId=c.CustomerId JOIN customer_address a ON a.customerid=i.customerid AND i.date BETWEEN a.FROM_DATE;

    (That syntax may not be quite right for SQL Server!)

  3. #3
    Join Date
    Jan 2004
    Location
    Slovakia
    Posts
    32
    I just want to know, if exists a feature in any of the today's DMBS's to manage the entity (record) changes or history without (sql)programatically adding history tables.
    (sz)Tomi

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •