EasyCFM.COM ColdFusion Forums / MS SQL Server Related Issues / sql server query help

   Reply to Discussion | New Discussion << previous || next >> 
Posted By Discussion Topic: sql server query help

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

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

I am really getting frustrated as I dont know what i am doing wrong.

The problem starts here:
-Batch Table:
register id
batch no

-Transaction Table:
Customer id
batch no
transaction number
Item id
qtysold

-Customer Table
customer id
customer name
customer address
** all other baggages with customer (lol)

-Item Table
Item id
Price
Cost
** etc..etc

I am trying to get the last Transaction Details from each register.I have 4 register.

As you can see that 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.

This is the query that i wrote but it just returns only  one single record  for the last transaction number made by that specific register.

SELECT max([Transaction].TransactionNumber) as Receipt No FROM Batch INNER Join[Transaction] ON
inner join transaction.customerid=customer.customerid on
inner join transaction.itemid=item.item id on
Batch.BatchNumber=[Transaction].BatchNumber WHERE (Batch.RegisterID = 1)

This query  returns only one record that mentions the transaction number but it should show all items for that transaction number..
what am i doing wrong..please help!!

Scarecrow
03-26-2007 @ 2:19 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Junior Member
Posts: 109
Joined: Oct 2004

You are telling the query to only return records for registerid = 1 ??

Take out the where clause and it should return a record (max) transaction number for each id.  Although I would expect that you would also need to return the registerid to do something with it.

Ken

www.krcaldwell.com

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

Thanks for replying but the problem is if i dont use max..it will return all the transaction made from that specific register. But i am not trying to do that. I am trying to ge the last record that has been inserted from that register.


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

I did not say not to use max !!

I said to take out the where clause

SELECT max([Transaction].TransactionNumber) as Receipt No FROM Batch INNER Join[Transaction] ON
inner join transaction.customerid=customer.customerid on
inner join transaction.itemid=item.item id on
Batch.BatchNumber=[Transaction].BatchNumber


Ken

www.krcaldwell.com

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

well thanks again to reply back..but if i dont use the where clause how am i going to get the results from that specific register... register =4? or register=3?

Scarecrow
03-26-2007 @ 10:14 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Junior Member
Posts: 109
Joined: Oct 2004

Okay, now I'm confused.

Don't you want to return a recordset that contains the max transaction number for each of the registers ?

Sorry, forgot the group by clause

SELECT max([Transaction].TransactionNumber) as Receipt No, Batch.RegisterID
FROM Batch INNER Join[Transaction]
ON
inner join transaction.customerid=customer.customerid on
inner join transaction.itemid=item.item id on
Batch.BatchNumber=[Transaction].BatchNumber
GROUP BY Batch.RegisterID

This query should return 4 records (or how ever many registers there are.  each record will contain the max transaction record and the register id.

Ken

www.krcaldwell.com

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

ok...i think i am confusing everyone..let me explain in details and it might help...All i am tryin to do is to print out  the last receipt from that register. but it only gives me the receipt no..but not what items are in that receipt no...for example when i query the table it gives me the last transcation number(receipt no) from that specific register:
for ex:
23456---receipt no

but i want what items has been bought that contains the receipt no... I cant pass any parameter other than the register id..so i want the result like this:

Item description    Price   qty   receipt no
----------------    -----   ---- ------------
gatorade             1.99    2    23456
juice                1.29    3    23456
candy                0.70    4    23456

.........
I dont know that still i made myself clear or not

Scarecrow
03-26-2007 @ 10:58 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Junior Member
Posts: 109
Joined: Oct 2004

Then you have to include the columns in the select clause, but because you are using an aggregate (max) you also have to include the columns in the group by clause

Select max(column) as column, column2, column3
From tables
where column4 = 1
group by column2, column3

Ken

www.krcaldwell.com

chenn
03-28-2007 @ 11:44 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
New Member
Posts: 4
Joined: Feb 2004

Correct me if I'm wrong, you want the details from the last transaction?  If so, Scarecrow, that isn't exactly going to work.  That will return multiple rows.  Faisalguru, you need to filter (where clause) on the max.  I don't have a sql server with me (Oracle by day, MS by night) to test, but here is the logic and my best shot at the actual sql...

First you need to get the max transaction based on the register (only return 1 column and 1 row):
  
SELECT max([transaction].transactionnumber)
FROM [Transaction] inner join Batch
  on batch.batchnumber = [transaction].batchnumber
WHERE batch.registerid = 1

Next you need the details (all details will return):

SELECT item, price, quantity, etc.
FROM tables join tables on xxx = xxx

Combine the two:

SELECT item, price, quantity, etc.
FROM tables join tables on xxx = xxx
WHERE [transaction].transactionnumber IN(
  SELECT max([transaction].transactionnumber)
  FROM [Transaction] inner join Batch
    on batch.batchnumber = [transaction].batchnumber
  WHERE batch.registerid = 1
)

Notice "IN" clause in the "WHERE" clause.  This will filter your entire details list by the max transaction number for the register.  Hopefully this sheds some light on your situation.

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

Thanks Mr.Chenn..you saved the whole year!!lol..It worked fine .
THanks goes both to mr.ken and chenn..it was very very helpful.

take care guys!! ( pimp of SQL's) lol


Website Designed and Developed by Pablo Varando.