Date: 06/09/06 (Asp Dot Net)    Keywords: database, asp, sql, web

My struggles continue.





Ok so I've been assigned the daunting task of setting up our documents to be sent to our new content management system from our current one which was written by a bunch of dumb brits who stole 80% of their code from Wrox. I need to bring out all the current and old documents. They are housed
in 1 database in 2 tables. All the current documents are in 1 table, and all their old versions are in the other. They use keys to communicate with each other, so every versioned document references the id of its current document. Also, not every current document has more than one version, so not every document in the current table has a corresponding record in the old table (the information in these tables is not totally consistent either, but that's a whole other issue that I'm not delving into now.)

The problem I'm having is that while my query will return all versioned records and current records WITHOUT any subsequent versions, it does NOT return the most current record of the versions. What I mean is that if there is four versions of a document, 3 of the documents are in the old table and 1 (the current version) is in the current table. My query only returns the 3 from the old table, and not the most recent one which resides on the current table.

My query looks like this. Some of the information is extraneous. The bold part is the main function I feel refers to this.

SELECT d.mdItemID AS CurrentID, d.mdFileDescription AS CurrentFile, d.mdFileName as CurrentFileName, d.mdCreatedByUser AS Creator, d.mdCreatedDate AS SubmittedDate,
tblMics_Docs_Versions.mvSubItemID AS OldID, tblMics_Docs_Versions.mvVersionedDate AS VersionedDate,
tblMics_Docs_Versions.mvFileName as OldFileName, tblMics_Docs_Versions.mvNotes AS VersionNotes,
tblDepartment.dpNAME AS DepartmentName FROM tblMics_Docs AS d INNER JOIN
tblDepartment ON d.mdDepartmentID = tblDepartment.dpID LEFT OUTER JOIN tblMics_Docs_Versions ON
d.mdItemID = tblMics_Docs_Versions.mvItemID
WHERE d.mdArchived = 0 ORDER BY mdItemID ASC, VersionedDate ASC

tblMics_Docs is the current table, tblMics_Docs_Versions is the old table.

People have suggested using temp tables or a UNION command to fix this issue, but the problem is that I got a 2.5 in SQL in college and its not my strong suit.


This is for a program that's being written in C# and binding to a datagrid (the webform version, NOT the ASP.Net version.) I figured this would be a good place to ask this question. If anyone could help me with this it would be greatly appreciated. Thanks.

Source: http://community.livejournal.com/aspdotnet/70280.html