sql server programming: I'll simplify the table structure that I've inherited in order to try to
explain what I need.

Three tables - ISSUES, USERS and ASSIGN:

ISSUES
IDRecord - Primary Key
Description
DateEntered

USERS
IDRecord- Primary Key
LastName
FirstName

ASSIGN
IDRecord - Primary Key
IDDefRec - matches to IDRecord in ISSUES
IDUser - matches to IDRecord in USERS


What I want is a result set for all ISSUES entered after 7/1/2005 (for
example) that includes all of the columns from ISSUES and the FirstName and
LastName of the last user assigned to the ISSUE. The ASSIGN table can
contain many rows per ISSUE as subsequent USERS are assigned to the ISSUE.
So I figure I just need to get the TOP 1 of the ASSIGN table that matches
the ISSUE and get the corresponding USER name. I just can't figure out how
to do it in one SELECT statement.

Jeff

What the first rule of a data model?? A data element has one and only
one name in a schema. So what is this magical "record_id" that appears
to be everywhere?

And why don' t you know that a row and record are totally different
concepts? Why don't you use ISO-8601 Standard date formats? Why did
you put the qualifier in the front of the names, in violation of the
ISO-11179 rules for metadata?

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.

If you knew what a key was, followed ISO Standards, and underdstood DRI
action your non-existent DDL would look like this:

Why is there no resolution date in your issues? The model of time in
SQL is durations, not single dates.

CREATE TABLE Issues
(issue_nbr INTEGER NOT NULL PRIMARY KEY,
issue_description VARCHAR(255) NOT NULL);

Create a dummy user zero called "To Be Determined" or '{{TBD}}' for
when an issue arrived if you don't assign them immediately.

CREATE TABLE Users
(user_id INTEGER DEFAULT 0 PRIMARY KEY,
last_name VARCHAR(20) NOT NULL,
first_name VARCHAR(20) NOT NULL)

CREATE TABLE Assignments
(issue_nbr NOT NULL
REFERENCES Isuses (issue_nbr)
ON UPDATE CASCADE
ON DELETE CASCADE,
user_id INTEGER DEFAULT '{{TBD}}' NOT NULL
REFERENCES Users (user_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
PRIMARY KEY (issue_nbr, user_id)
assigned_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
resolved_date DATETIME, -- null means still open);
CHECK (assigned_date <= resolved_date));

Now you have a whole tracking history.

[quoted text, click to view]

SELECT @my_date, I1.issue_nbr, I1.issue_description,
U1.user_id, U1.last_name, U1.first_name
FROM Issues AS I1, Assignments AS A1, Users AS U1
WHERE @my_date BETWEEN A1.assigned_date AND A1.resolved_date
AND U1.user_id = A1.user_id
AND A1.issue_nbr = I1.issue_nbr;

[quoted text, click to view]


It appears that you missed the part where I said that I INHERITED this
structure. This is an application that the school district I work for
purchased and I have NO control over its structure. It is what it is. I
simply need to know if, given the structure that I laid out, is there a way
to return for each item in the ISSUES table beyond a parameterized date the
first and last name of the User last assigned to the Issue in the ASSIGN
table as well as all of the details of that Issue.

js

You are screwed. Would you like an expert witness for the lawsuit?

[quoted text, click to view]

;}

I guess I'll just write a stored procedure to move the records to a temp
table then and look up the User name against the Temp result set. Just was
looking for a quicker way.

js

SELECT I.*, U.FirstName, U.LastName
FROM Issues I
INNER JOIN
(SELECT IDDefRec,
MAX(IDUser) As IDUser
FROM Assign
GROUP BY IDDefRec) A
ON I.IDRecord = A.IDDefRec
INNER JOIN USERS U
ON A.IDUser=U.IDRecord

--The above example just get the max of userid. To get the last assigned
userid, you have to add another column
in the ASSIGN table to keep track of the time of assignment.

--
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com


[quoted text, click to view]

[quoted text, click to view]

That will not work. The schema does not show when someone was assigned
to an issue, only when the issue was first entered. Created on Monday,
assigned to Tom on Tuesday, handed off to Wendy on Wednesday and thrown
to Thomas on Thursday.

The design is flawed.

[quoted text, click to view]

I'm not sure that someone that is mentally unstable would qualify as an expert
witness. ;->


Thomas

On Mon, 25 Jul 2005 09:03:17 -0700, "Thomas Coleman" <replyingroup@anywhere.com>
[quoted text, click to view]
in <eeuGRJTkFHA.1444@TK2MSFTNGP10.phx.gbl>

[quoted text, click to view]

I sure hope your insults are tongue in cheek because they have no place in a
professional newsgroup. Or are you NOT a professional?


---

[quoted text, click to view]

My sententious observations about Don Celko's behavior are as tongue and cheek
as his remarks. ;->


Thomas

[quoted text, click to view]
I'll agree, there's more tongue *and* cheek here than tongue *in* cheek,
and you're
responsible for the cheek. (Not that that's that bad.)