Help with query required...

**--CELKO--** · Jul 20 '05, 01:16 AM

Re: Help with query required...

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,
..);

CustomerManager s
(cust_nbr INTEGER NOT NULL
REFERENCES Customers(cust_ nbr)
ON UPDATE CASCADE
ON DELETE CASCADE,
manager_nbr INTEGER NOT NULL
REFERENCES Managers(manage r_nbr)
ON UPDATE CASCADE
ON DELETE CASCADE,
start_date DATETIME DEFAULT CURRENT_TIMESTA MP NOT NULL
end_date DATETIME, -- null means current
CHECK (start_date <= end_date),
PRIMARY KEY (cust_nbr, manager_nbr, start_date));
[color=blue][color=green]
>> What I want to do is extract, in one query, a list of all customers[/color][/color]
and their *current* associated manager, <<

Put this in a VIEW:

SELECT cust_name, manager_nbr, start_date, CURRENT_TIMESTA MP
FROM Customers AS C1,
CustomerManager s AS M1
WHERE M1.cust_id = C1.cust_id
AND M1.end_date IS NULL;

Much easier with the right data model!

**John Gilson** · Jul 20 '05, 01:16 AM

Re: Help with query required...

"digitalfis h" <digital.fish@n tlworld.com> wrote in message
news:Gqn1b.2773 847$mA4.379928@ news.easynews.c om...[color=blue]
> I have two related tables in my SQL database that I wish to join as follows:
>
> ------------------------------------
>
> tblCustomers
> ID (pk)
> Name
> etc.
>
> tblCustomerMana gers
> 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
>
> tblCustomerMana gers
> 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 tblCustomerMana gers ON tblCustomerMana gers.[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[/color]

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 tblCustomerMana gers
(
id INT NOT NULL PRIMARY KEY,
customerid INT NOT NULL REFERENCES tblCustomers (id),
manager VARCHAR(20) NOT NULL,
startdate DATETIME NOT NULL
)

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

SELECT C.name, M.manager, M.startdate
FROM tblCustomers AS C
INNER JOIN
tblCustomerMana gers AS M
ON M.startdate <= CURRENT_TIMESTA MP AND
M.customerid = C.id AND
NOT EXISTS (SELECT *
FROM tblCustomerMana gers AS M2
WHERE M2.startdate > M.startdate AND
M2.startdate < CURRENT_TIMESTA MP 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

Help with query required...

Help with query required...

Comment

Comment