EasyCFM.COM ColdFusion Forums / MS SQL Server Related Issues / Sql Query!!Help

   Reply to Discussion | New Discussion << previous || next >> 
Posted By Discussion Topic: Sql Query!!Help

book mark this topic Printer-friendly Version  send this discussion to a friend  new posts last

faisalguru
03-10-2007 @ 3:38 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
New Member
Posts: 29
Joined: Oct 2006

Al right big bosses..I have a query but it is not working for some reason.

I have a :

--transaction table
--batch table
I am trying to get the total sales for each registers.
But the transaction table doesnot have the column for register id..but batch table does have the column for register id .but transaction table have the column for batchnumber. so basically the batch table and transaction table is linked by the batchnumber.
Now the question is the batch number is not constant.It changes whenever a new transaction takes place. How can I query my transaction table to get the max number of the batch for that specific register.

this is my query:
SELECT Batch.RegisterID, Batch.BatchNumber, [Transaction].TransactionNumber FROM Batch INNER Join[Transaction] ON Batch.BatchNumber=[Transaction].BatchNumber WHERE (Batch.RegisterID = 1) AND (Batch.BatchNumber =(SELECT     MAX(BATCHNUMBER)FROM BATCH))

My registerid is constant but not the batch numbers.

mquack
03-10-2007 @ 4:31 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Moderator
Posts: 1544
Joined: Jan 2005

Can you give us more details on your table design (e.g. column names and datatypes) for the 'registers', 'transaction', and 'batch' tables?

I may be misunderstanding what you're attempting to do, but with the limited information you've provided I'm inclined to think that you need to make some changes to your database design.  At the very least, I would definitely suggest changing the name of your transaction table.  'Transaction' is a reserved word in various languages and databases, so using it as a table name (or column name) could potentially lead to problems.

Post your design if you can, and a more detailed description of the desired result.  That will help us in assisting you.


www.rachelqueensg.com
Amazon Wish List

Scarecrow
06-07-2007 @ 7:40 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Junior Member
Posts: 109
Joined: Oct 2004

Already been done......

This message was edited by Scarecrow on 6-7-07 @ 7:43 PM


Website Designed and Developed by Pablo Varando.