Help with query required...

[digitalfish]

I have two related tables in my SQL database that I wish to join as follows:

------------------------------------

tblCustomers
ID (pk)
Name
etc.

tblCustomerManagers
ID (pk)
CustomerID (fk)
Manager (this *is* an fk but for the purposes of demonstration is
not)
StartDate (indicates the date upon which the manager took / is taking
control of the company)

------------------------------------

Example entries are:

tblCustomers
1 Microsoft
2 Symantec
3 Borland

tblCustomerManagers
1 1 Barry 01/01/03
2 1 Peter 01/07/03
3 2 Norman 01/02/03
4 3 Terry 01/01/03
5 3 Peter 01/07/05

------------------------------------

What I want to do is extract, in one query, a list of all customers and
their *current* associated manager, so the result set today would be:

Microsoft Peter 01/07/03
Symantec Norman 01/02/03
Borland Terry 01/01/03
Currently I have:
SELECT [Name], [Manager], [StartDate]
FROM tblCustomers
INNER JOIN tblCustomerManagers ON tblCustomerManagers.[CustomerID] =
tblCustomers.[ID]
WHERE [StartDate] <= GETDATE()
ORDER BY [Name], [StartDate] DESC

but this obviously returns multiple entries for customers having managers
prior to today eg:

....
Microsoft Peter 01/07/03
Microsoft Barry 01/01/03
....

I know this is a simple question but I cannot think of a way of doing it
without making the query extremely complicated.

Any help is appreciated,
Thanks,
df

[--CELKO--]

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in
your schema are. Please read ISO-11179 and a book on data modeling;
what you did post is wrong. Name data elements for what they mean in
the model, NOT for how they are PHYSICALLY stored! If the first table
is really a model of the customers who bought tables and you have a
"chairCustomers", "stoolCusotmers", etc. table I apologize :)

There is no such thing as a global, magical, universal ID in the
RDBMS. To be is to be something in particular; to be nothing in
particular or everything in general is to be nothing.

Entities have duration, not a point in time, so your design is wrong.
Look up a column I did in INTELLIGENT ENTERPRISE website on the topic
of time. You also avoided any natural keys, so the schema has no data
integrity. Try this:

CREATE TABLE Customers
(cust_nbr INTEGER NOT NULL PRIMARY KEY,
cust_name CHAR(35) NOT NULL,
..);

CustomerManagers
(cust_nbr INTEGER NOT NULL
REFERENCES Customers(cust_nbr)
ON UPDATE CASCADE
ON DELETE CASCADE,
manager_nbr INTEGER NOT NULL
REFERENCES Managers(manager_nbr)
ON UPDATE CASCADE
ON DELETE CASCADE,
start_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL
end_date DATETIME, -- null means current
CHECK (start_date <= end_date),
PRIMARY KEY (cust_nbr, manager_nbr, start_date));

What I want to do is extract, in one query, a list of all customers

and their *current* associated manager, <<

Put this in a VIEW:

SELECT cust_name, manager_nbr, start_date, CURRENT_TIMESTAMP
FROM Customers AS C1,
CustomerManagers AS M1
WHERE M1.cust_id = C1.cust_id
AND M1.end_date IS NULL;

Much easier with the right data model!

[John Gilson]

"digitalfish" <di**********@ntlworld.com> wrote in message
news:Gq**********************@news.easynews.com...

I have two related tables in my SQL database that I wish to join as follows:

------------------------------------

tblCustomers
ID (pk)
Name
etc.

tblCustomerManagers
ID (pk)
CustomerID (fk)
Manager (this *is* an fk but for the purposes of demonstration is
not)
StartDate (indicates the date upon which the manager took / is taking
control of the company)

------------------------------------

Example entries are:

tblCustomers
1 Microsoft
2 Symantec
3 Borland

tblCustomerManagers
1 1 Barry 01/01/03
2 1 Peter 01/07/03
3 2 Norman 01/02/03
4 3 Terry 01/01/03
5 3 Peter 01/07/05

------------------------------------

What I want to do is extract, in one query, a list of all customers and
their *current* associated manager, so the result set today would be:

Microsoft Peter 01/07/03
Symantec Norman 01/02/03
Borland Terry 01/01/03
Currently I have:
SELECT [Name], [Manager], [StartDate]
FROM tblCustomers
INNER JOIN tblCustomerManagers ON tblCustomerManagers.[CustomerID] =
tblCustomers.[ID]
WHERE [StartDate] <= GETDATE()
ORDER BY [Name], [StartDate] DESC

but this obviously returns multiple entries for customers having managers
prior to today eg:

...
Microsoft Peter 01/07/03
Microsoft Barry 01/01/03
...

I know this is a simple question but I cannot think of a way of doing it
without making the query extremely complicated.

Any help is appreciated,
Thanks,
df

CREATE TABLE tblCustomers
(
id INT NOT NULL PRIMARY KEY,
name VARCHAR(20) NOT NULL
)

INSERT INTO tblCustomers (id, name)
VALUES (1, 'Microsoft')
INSERT INTO tblCustomers (id, name)
VALUES (2, 'Symantec')
INSERT INTO tblCustomers (id, name)
VALUES (3, 'Borland')

CREATE TABLE tblCustomerManagers
(
id INT NOT NULL PRIMARY KEY,
customerid INT NOT NULL REFERENCES tblCustomers (id),
manager VARCHAR(20) NOT NULL,
startdate DATETIME NOT NULL
)

INSERT INTO tblCustomerManagers (id, customerid, manager, startdate)
VALUES (1, 1, 'Barry', '20030101')
INSERT INTO tblCustomerManagers (id, customerid, manager, startdate)
VALUES (2, 1, 'Peter', '20030701')
INSERT INTO tblCustomerManagers (id, customerid, manager, startdate)
VALUES (3, 2, 'Norman', '20030201')
INSERT INTO tblCustomerManagers (id, customerid, manager, startdate)
VALUES (4, 3, 'Terry', '20030101')
INSERT INTO tblCustomerManagers (id, customerid, manager, startdate)
VALUES (5, 3, 'Peter', '20050701')

SELECT C.name, M.manager, M.startdate
FROM tblCustomers AS C
INNER JOIN
tblCustomerManagers AS M
ON M.startdate <= CURRENT_TIMESTAMP AND
M.customerid = C.id AND
NOT EXISTS (SELECT *
FROM tblCustomerManagers AS M2
WHERE M2.startdate > M.startdate AND
M2.startdate < CURRENT_TIMESTAMP AND
M2.customerid = M.customerid)

name manager startdate
Microsoft Peter 2003-07-01 00:00:00.000
Symantec Norman 2003-02-01 00:00:00.000
Borland Terry 2003-01-01 00:00:00.000

Regards,
jag