Doht! warning N00b needs help with a query

Last Post 21 Jan 2003 05:03 PM by BobBarker. 8 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
Rmalsbur
New Member
New Member

--
08 Jan 2003 10:19 AM

I've been trying to find out how i can do the following

Select receipt_number and register_Id from transaction_log where tran_cd=121

the results will be a list of transactions and the cash registers they were processed on

3434 1
4454 2
5555 1

i want to take the resulting list of transactions and run a query for each line
Select * from items_purchased_details where receipt_number =1 and register_id= 1

This query is to find all the aborted transactions in a store [ the tran_cd 121 = aborted transaction] and then pull all details for that receipt from the items_purchased_details

any help would be appreciated


BobBarker
New Member
New Member

--
08 Jan 2003 10:54 AM
Yes you are a NOOb aren't you? This is a rather simple join. If you can't join two tables together in a query you're not going to get too far with databases!

Here is the solution. I encourage you to look up all the keywords in BOL so you can see why this works.

SELECT i.*
FROM items_purchased_details i
INNER JOIN transaction_log t
ON t.receipt_number = i.receipt_number
AND t.register_id = i.register_id
WHERE t.tran_cd = 121
ORDER BY register_id, receipt_number

BB
Rmalsbur
New Member
New Member

--
08 Jan 2003 11:09 AM
most of my experience with sql is backing it up, installing it and making tables
and dropping stuff, or truncating (thanx to my last post on sqlmag.com)
mostly with osql


I will hit the BOL, just wasnt sure what ya call what i was trying to do (merge/join/marry/addto?) lol









Rmalsbur
New Member
New Member

--
08 Jan 2003 11:18 AM
Where are my manners Thank you Very much

(I hate the rule that says now matter how much you try NOT to act like a N00b, it justs make that fact more obvious).
BobBarker
New Member
New Member

--
08 Jan 2003 11:26 AM
Marry... that's a good one.... Maybe when I invent my own programming language I'll have to include MARRY and DIVORCE as keywords. After all, if use the SPAWN keyword, shouldn't you have to invoke MARRY beforehand?

BB
Rmalsbur
New Member
New Member

--
08 Jan 2003 11:42 AM
dont forget PRENUP instead of rollback


Rmalsbur
New Member
New Member

--
21 Jan 2003 04:35 PM
I've piled on more BS and it works ok.
I do however have one question. I often find the BOL difficult to understand
and my experience is "topic" blah is the result of "word1" and sometimes" referred to
this or that ... i attempt to look up one thing and i find out i need to know at least 15
other terms and how they are used. I realize this is probably easy for people coming
from some type of background but im one of those slow people.

I guess i need a primer ... I also get impatient ... instead of telling me to RTFM .. is
there a resource or a website where it starts off with one simple query and then slowly builds and builds and compare differences?

BTW bob .. i got this far, YES ive put in over 40 hours ripping stuff out of the SQL Server Black Book and other posts in here (not much transact SQL in it)

declare @dToday datetime
set @dToday = getdate()
select transaction_header.RGST_ID,"test"=count(emp_id),TRANSACTION_HEADER.BUS_DT,
transaction_header.str_id,transaction_header.emp_id,transaction_header.tran_id,
"Total"=SUM(ln_detail.rtl_prc)
from
transaction_header
JOIN ln_detail ON (transaction_header.tran_id=ln_detail.tran_id)
where transaction_header.tran_cd=121 AND
LN_DETAIL.VOID_CD=4 AND
rtl_prc < 5000.00 AND
datediff(dd,bus_dt,@dToday)<=5
group by
TRANSACTION_HEADER.BUS_DT,
transaction_header.emp_id,
transaction_header.tran_id,
transaction_header.str_id,
transaction_header.rgst_id,
ln_detail.rtl_prc
order by rtl_prc desc,TRANSACTION_HEADER.STR_ID
BobBarker
New Member
New Member

--
21 Jan 2003 05:03 PM
I'm surprised you couldn't find a tutorial online somewhere. I'm sure one of the Forum Pros will direct you to some SqlMag articles.

Did you have a specific about that SQL you posted?

BB
satya
New Member
New Member

--
22 Jan 2003 05:53 AM
Even this SQL Course has got valid information for SQL beginners.
You are not authorized to post a reply.

Acceptable Use Policy