May 16, 2006 at 9:15 pm
Hi all,
I'm trying to write a query, but i'm a little out of my depth!
The situation is as follows: I am making some modifications to a database. Originally there was one comment field per 'stakeholder' record. I've made a new comments table so that stakeholders can have more than one comment. The comment table has the following fields:
commentID, time, stakeholderID, comment and username (username is the username of the person who entered the comment)
In the existing system, everytime a modification was made to a stakeholder record, a record was entered into the history table. The history table has the following fields:
historyID, stakeholderID, field, username, time and previousVal.
If the comment field was updated, 'field' would have 'comment' written in it.
To move the existing comments from the stakeholder table to the comments table, I was thinking of doing the following:
1. If there is an entry in the history table for a StakeholderID that has a comment, then insert the time and username from the latest of these records into the comment table for that stakeholder.
2. If there is no entry in the history table for that StakeholderID, and they have a comment in the comment table, write 'unknown' for the username and '1900-01-01 00:00:00' in the time field.
This is the query I have made to select the records:
select stakeholders.stakeholderID, stakeholders.comment, history.time, history.username
from stakeholders INNER JOIN history
on stakeholders.stakeholderID = history.stakeholderID
where stakeholders.comment is not NULL
and stakeholders.comment <> ''
and (history.stakeholderID, history.time) in
(select distinct history.stakeholderID, Max(history.time) from history where history.field = 'comment' GROUP BY history.stakeholderID)
however I get the error 'Line 6: Incorrect syntax near ','.'.
How can I match two fields to the select subquery?
I hope the above is clear! Any help would be apprciated.
Regards,
Daniel
May 16, 2006 at 11:35 pm
Try rewriting you query like so:
select stakeholders.stakeholderID, stakeholders.comment, history.time, history.username
from stakeholders INNER JOIN history
on stakeholders.stakeholderID = history.stakeholderID
left outer join (
select distinct history.stakeholderID as stakeholderID, Max(history.time) as [time] from history where history.field = 'comment' GROUP BY history.stakeholderID
) d on history.stakeholderID = d.stakeholderID and history.time = d.[time]
where stakeholders.comment is not NULL
and stakeholders.comment <> ''
Note that I have not looked at your 'logic' per se, so you will have to carefully consider the data to determine if your query does what it's suppose to. Also note the LEFT OUTER JOIN clause - change this when you are happy with the data that's being returned.
May 16, 2006 at 11:58 pm
Hi Paul,
Thanks, I think that's got it!
I just needed to change the left outer join to an inner join to not get the multiple records (?)
Cheers,
Daniel
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply