Nested Select to Join three tables into one result set

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.
JeffWhat 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.
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;|||> 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?
> Why is there no resolution date in your issues? The model of time in
> SQL is durations, not single dates.
>
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?|||> You are screwed. Would you like an expert witness for the lawsuit?
;}
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
"Jeff Swanberg" <jswanberg@.swanbergcomputing.com> wrote in message
news:%23GMlmu$jFHA.3580@.TK2MSFTNGP09.phx.gbl...
> 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
>
>|||>> I'll just write a stored procedure to move the records [sic] to a temp table t
hen and look up the User name against the Temp result set. <<
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.|||> You are screwed. Would you like an expert witness for the lawsuit?
I'm not sure that someone that is mentally unstable would qualify as an expe
rt
witness. ;->
Thomas|||On Mon, 25 Jul 2005 09:03:17 -0700, "Thomas Coleman" <replyingroup@.anywhere.
com>
wrote:
in <eeuGRJTkFHA.1444@.TK2MSFTNGP10.phx.gbl>

>I'm not sure that someone that is mentally unstable would qualify as an exp
ert
>witness. ;->
>
>Thomas
I sure hope your insults are tongue in ch because they have no place in a
professional newsgroup. Or are you NOT a professional?
Stefan Berglund|||> I sure hope your insults are tongue in ch because they have no place in ad">
> professional newsgroup. Or are you NOT a professional?
My sententious observations about Don Celko's behavior are as tongue and che
ek
as his remarks. ;->
Thomas