Help - I am fairly new at MS SQL, but understand the concepts and have looked at the code in MS Access many times. But here's my problem:
I need to create a complex query across multiple tables within the same database, but I'm just not sure how to do it within MS SQL. I've done it in Access, creating separate queries and then created a query on those queries, but it takes a long time to run (and I don't trust the results from Access). Here's the code that does work, but it specifies criteria that I really want to query (in the WHERE clause, what is in bold):
SELECT Tracking.TransactionNumber, Min(Tracking.DateTime) AS DateTime, WorldCatInformation.OCLCNumber, WorldCatInformation.Type, WorldCatInformation.Data, Transactions.RequestType, Transactions.DocumentType, Transactions.CallNumber, Transactions.Location, UsersALL.NVTGC, UsersALL.Organization, UsersALL.Department, UsersALL.Status, UsersALL.LastName, Transactions.CitedIn, Transactions.ESPNumber, Transactions.ISSN, Transactions.SystemID, Transactions.LoanAuthor, Transactions.LoanTitle, Transactions.CitedVolume, Transactions.LoanEdition, Transactions.LoanPlace, Transactions.LoanPublisher, Transactions.LoanDate, Transactions.PhotoArticleAuthor, Transactions.PhotoArticleTitle, Transactions.PhotoItemAuthor, Transactions.PhotoItemEdition, Transactions.PhotoItemPlace, Transactions.PhotoItemPublisher, Transactions.PhotoJournalTitle, Transactions.PhotoJournalYear, Transactions.PhotoJournalMonth, Transactions.PhotoJournalVolume, Transactions.PhotoJournalIssue, Transactions.PhotoJournalInclusivePages, Transactions.LendingLibrary, Transactions.LendingString, Transactions.TransactionDate, Transactions.TransactionStatus, Transactions.ReasonForCancellation
FROM WorldCatInformation INNER JOIN Transactions ON WorldCatInformation.TransactionNumber = Transactions.TransactionNumber INNER JOIN
UsersALL ON Transactions.Username = UsersALL.UserName INNER JOIN
Tracking ON Transactions.TransactionNumber = Tracking.TransactionNumber
WHERE Tracking.DateTime Between ('1/1/2008') And ('1/31/2008') And UsersALL.NVTGC <> 'BIOMED' AND UsersALL.NVTGC <> 'LAWILS' AND [B]WorldCatInformation.Type = 'Call Number (LC)'[/B]
GROUP BY Tracking.TransactionNumber, WorldCatInformation.OCLCNumber, WorldCatInformation.Type, WorldCatInformation.Data, Transactions.RequestType, Transactions.DocumentType, Transactions.CallNumber, Transactions.Location,
UsersALL.NVTGC, UsersALL.Organization, UsersALL.Department, UsersALL.Status, UsersALL.LastName, Transactions.CitedIn, Transactions.ESPNumber, Transactions.ISSN, Transactions.SystemID, Transactions.LoanAuthor, Transactions.LoanTitle, Transactions.CitedVolume, Transactions.LoanEdition, Transactions.LoanPlace, Transactions.LoanPublisher, Transactions.LoanDate, Transactions.PhotoArticleAuthor,
Transactions.PhotoArticleTitle, Transactions.PhotoItemAuthor, Transactions.PhotoItemEdition, Transactions.PhotoItemPlace,
Transactions.PhotoItemPublisher, Transactions.PhotoJournalTitle, Transactions.PhotoJournalYear, Transactions.PhotoJournalMonth,
Transactions.PhotoJournalVolume, Transactions.PhotoJournalIssue, Transactions.PhotoJournalInclusivePages, Transactions.LendingLibrary,
Transactions.LendingString, Transactions.TransactionDate, Transactions.TransactionStatus, Transactions.ReasonForCancellation;I've tried using UNION, but that won't work since the columns & rows are not identical in type. I thought about a JOIN and have tried this, removing the bold portion out and putting this code at the end:
(SELECT MIN(WorldCatInformation.TransactionNumber) AS FirstOfTransactionNumber, WorldCatInformation.OCLCNumber, WorldCatInformation.Data
FROM WorldCatInformation INNER JOIN Transactions ON WorldCatInformation.TransactionNumber=Transactions.TransactionNumber
WHERE WorldCatInformation.Type = 'Call Number (LC)'
GROUP BY WorldCatInformation.OCLCNumber, WorldCatInformation.Data, WorldCatInformation.Type)But this creates a two tables.
A secondary problem is the SELECT MIN. In Access, there is the option to select FIRST, but I have tried that over and over in MS SQL and it says it does not recognize FIRST. I really need to just find the first occurrence of each instance so I'm not sure how to do that within MS SQL. Any advice on either one of these issues would be greatly appreciated. Thank you!
Jamen McGranahan
jmcgranahan at tds dot net