Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Database > SQL Language
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old April 7th, 2004, 05:06 AM
Registered User
 
Join Date: Apr 2004
Location: Dublin, , Ireland.
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Complex (to me) nested queries spanning 4 tables!

This is what I have needed to do so far:

1. Select all the rows in table TransactionEntry that have column SalesRepID = 0

2. These rows have two columns I need to work with, the first is ItemID and the second is Quantity - I need to get the total Quantity for all items with the same ID and I need to then use the ItemID value to select all the rows in the table Item that have ID equal to the ItemID in TransactionEntry

3. From these rows in Item I need to get the value of their column ItemLookupCode - I then need to pair the ItemLookupCode from Item with the total quantity for that ItemID in TransactionEntry

Now, to do that I have spent hours and hours creating this query:

Code:
SELECT Item.ItemLookupCode, Sum(TransactionEntry.Quantity) AS QtySum 
FROM Item INNER JOIN TransactionEntry ON Item.ID = TransactionEntry.ItemID 
GROUP BY Item.ItemLookupCode, TransactionEntry.SalesRepID HAVING TransactionEntry.SalesRepID = 0
This works quite well and I am able to do that I need with it. Now though I would like to be able to generate purchase orders from this information. These are stored in another table. (By the way, this is work for the church / youth club where I grew up, they run a shop to try and fund the activities they do because it's a pretty poor neighbourhood. I'm trying to do this for them to remove as much of the technical stuff as possible for them.)

To generate a purchase order I need to do the following:

4. Insert a new row into table PurchaseOrder with ID and PONumber that are not necesarily equal but are both 1 higher than their previous values. I think ID can auto increment but I don't think PONumber can. I also need to set Status equal to 0, a column named DateCreated should have a value of DD/MM/YYYY

5. Insert a new row into table PurchaseOrderEntry for each Item from step 2 above with ID that auto increments, PurchaseOrderID that is the same as the ID in PurchaseOrder table and ItemID is the same as the ID from step 2 above and QuantityOrdered equal to the total quantity from step 2 above and the price should be equal to the Price field in the Item row as above.

Here's a quick text mockup to hopefully better explain my problem:

Code:
|--------------------------------|
|Table: TransactionEntry         |
|--------------------------------|
| SalesRepID | ItemID | Quantity |
|------------|--------|----------|
|      1     |    1   |    3     |<- this row will be ignored.
|      0     |    1   |    4     |<- |these two rows will have their
|      0     |    1   |    54    |<- |quantities added
|      0     |    2   |    43    |
|      0     |    3   |    33    |
|--------------------------------|

|-----------------------------|
|Table: Item                  |
|-----------------------------|
| ID | ItemLookupCode | Price |
|----|----------------|-------|
|  1 |     10001      | 24.95 |<- this row would be selected from the data above and it's quantity would be 57 (54 + 3) so I would have ItemLookupCode = 10001, Quantity = 57
|  2 |     10043      | 23.45 |<- ItemLookupCode = 10043, Quantity = 43 [as above]
|  3 |     20234      | 19.95 |
|-----------------------------|

The tables above will only be read from, not inserted to. All the tables below will have their contents inserted by me. These are the queries I need to create.

|--------------------------------------|
|Table: PurchaseOrder                  |
|--------------------------------------|
| ID | PONumber | Status | DateCreated |
|----|----------|--------|-------------|
|  1 |  000001  |    0   |  22/02/2004 |<- this would be a sample row.
|  2 |  000002  |    0   |  23/02/2004 |<- this is the type of row I'd like to insert with ID, PONumber being increased by 1 automatically, status always = 0 and DateCreated = the current DD/MM/YYYY
|--------------------------------------|

|---------------------------------------------------------|
|Table: PurchaseOrderEntry                                |
|---------------------------------------------------------|
| ID | PurchaseOrderID | ItemID | QuantityOrdered | Price |
|----|-----------------|--------|-----------------|-------|
| 15 |      00001      |   1    |        57       | 24.95 |<- sample row, PurchaseOrderID = PONumber from above, Price is the same price as the row in Item with ID = ItemID and QuantityOrdered = the total quantity from above
| 16 |      00002      |   2    |        43       | 23.45 |<-| both rows have the same PurchaseOrderID because that purchase order contained orders for Items 2 and 3
| 17 |      00002      |   3    |        33       | 19.95 |<-| this is of the parts I'm having most trouble with!
|---------------------------------------------------------|
I might not have explained all of this very well so if you have any questions just ask. I'd really appreciate if anyone could create a query to do this for me or even give me a point in the right direction.

Thanks a lot,
Regards,
Bob



Reply With Quote
  #2 (permalink)  
Old April 23rd, 2004, 10:54 AM
Authorized User
 
Join Date: Apr 2004
Location: , , Ireland.
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I've looked at this for a few minutes but I can't answer it because I can't figure out what determines when to create a new Purchase order.

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Nested Calculated Fields in Queries SerranoG Access 0 August 14th, 2006 10:04 AM
Oracle Nested Tables prabodh_mishra Pro VB Databases 1 April 26th, 2006 12:15 PM
Nested tables prabodh_mishra Oracle ASP 0 February 27th, 2006 09:59 AM
Blank fields - queries with many tables dvarrin Crystal Reports 0 February 1st, 2005 05:43 AM
Stored Procedure -- Nested queries pinkandthebrain SQL Server ASP 8 February 28th, 2004 07:55 PM



All times are GMT -4. The time now is 05:01 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.