MS SQL

MS SQL RSS

Complex query help

Please support our MS SQL advertiser: Intel Parallel Studio Home

•

Join Date: Aug 2008

Posts: 3

Reputation: jmcgranahan is an unknown quantity at this point

Solved Threads: 0

jmcgranahan jmcgranahan is offline

Offline

Newbie Poster

Complex query help

Aug 29th, 2008

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):

Help with Code Tags

(Toggle Plain Text)

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 WorldCatInformation.Type = 'Call Number (LC)'
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;

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 WorldCatInformation.Type = 'Call Number (LC)'
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:

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
(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)
(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

Last edited by peter_budo; Aug 29th, 2008 at 7:39 pm. Reason: Keep It Organized - please use [code] tags

•

Join Date: Aug 2008

Posts: 1,162

Reputation: dickersonka will become famous soon enough

Solved Threads: 137

dickersonka dickersonka is offline

Offline

Veteran Poster

Re: Complex query help

Aug 30th, 2008

to find the first in mssql use a order by with with select top 1

Custom Application & Software Development
www.houseshark.net

•

Join Date: Aug 2008

Posts: 3

Reputation: jmcgranahan is an unknown quantity at this point

Solved Threads: 0

jmcgranahan jmcgranahan is offline

Offline

Newbie Poster

Re: Complex query help

Sep 2nd, 2008

Well, not exactly. The command you suggest returns only the top entry in the table. That's not what I am looking for. In this table, there are multiple transactions per item, and what the user is looking for is the first transaction date per item. Is there a way I can do this?

Jamen McGranahan

•

Join Date: Sep 2008

Posts: 1

Reputation: Kandakis is an unknown quantity at this point

Solved Threads: 0

Kandakis

Offline

Newbie Poster

Re: Complex query help

Sep 4th, 2008

•

Originally Posted by jmcgranahan

Well, not exactly. The command you suggest returns only the top entry in the table. That's not what I am looking for. In this table, there are multiple transactions per item, and what the user is looking for is the first transaction date per item. Is there a way I can do this?

Jamen McGranahan

I don't think you're going to get away with trying to get all the data with just one query. Access SQL is quite different from Standard SQL. You may want to run several stored procs with order bys on the data to get just what you want. You may have to use temp tables as an intermediary step. Sorry, I've been there with Access. EX VB Programmer no longer working for Fortune 100 companies.