Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
SketchySteve
Starting Member
4 Posts |
 Posted - 2005-07-12 : 07:29:09
|
| Hi there, This is my first post so would first just like to hi, i'm sure i'll be back. I'm a web developer in Brighton and have been figuring out SQL server for the last 6 months (was fresh outa uni before that). Anways need a query which will act on two tables, tblTransactions which stores a list of every transaction on an e-commerce web site. This table has the following columns ID, Order#, Customer, Address, TransAmount The next table is one called tblTransactionProducts and contains each product from every transaction, its name, its price and how many were purchased in that order this table has the following columns. ID, Order#, ProductID, ProductDescription, Quantity, ProductQuantity Order # Customer Address 123456 Mr J Doe 1 Front Street ProductID ProductDescription Qty Value MME 3 Marvin's Magic Hat 1 £14.99 MME 1 125 Box of Tricks 1 £10.00 MM CC Marvin's Magic Carraige 1 £3.95 ORDER TOTAL £28.94 Order # Customer Address 123456 Mr J Doe 1 Front Street MM Item # Description Qty Value MME 3 Marvin's Magic Hat 1 £14.99 MME 1 125 Box of Tricks 1 £10.00 MM CC Marvin's Magic Carraige 1 £3.95 ORDER TOTAL £28.94 Any idea how to show each transaction with each product in that transaction underneath it. Even if you dont have time to give me a solution if you could let me know where I should be looking or show me an example / solution of a similer problem i'd be grateful. Had a good google for this dtuff first, but can be tricky finding something when you dont know what your looking for! |
|
|
AndyB13
Aged Yak Warrior
583 Posts |
Posted - 2005-07-12 : 07:50:39
|
Do it in your presentation layer/client (eg Access reports/web)AndyBeauty is in the eyes of the beerholder  |
 |
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2005-07-12 : 09:50:05
|
| if you already have the query to get these results, then do what Andy said, and handle it in the presentation layer. That is what it is for.*need more coffee*SELECT * FROM Users WHERE CLUE > 0(0 row(s) affected) |
|
|
|
SketchySteve
Starting Member
4 Posts |
Posted - 2005-07-12 : 12:23:02
|
| Cheers guys, always try to do as much as possible within the query rather than fron end. Nested record sets in asp or a bit nasty. Ah well soon be working with ASP.NET Datasets. Ah so much nicer.Thanks guys |
|
|
|
|
|
|
|
|
Grouping Tables With a One To Many Relationship