General information
Category:
Databases,Tables, Views, Indexing and SQL syntax
>>Hello all,
>>This is probably a question that has been asked numerous times, but I have an urgency.
>>
>>Scenario:
>>1 Sales Order Master Table and 2 detail tables
>>I need to select all SO's for a given customer from the master.(This is no problem). Next I need to select the item detail from 2 separate tables. the 2 detail tables need to link to the master by SOnumber, and to each other by SOnumber and ItemNumber.
>>
>>Any ideas? I'm racking my brain on this one. I have fallen on the wayside from coding anymore, but need to get this project out for the sales dept ASAP. ANY HELP WILL BE GREATLY APPRECIATED!!
>>
>>Thanks in advance,
>>Ken Morehouse, MCP
>
>If I understand you correctly, you are describing a cyclical relationship, which is illegal withing a single query. Could you explain more as to how the two child tables are related conceptually?
Sure. Each SO is listed in the somast.dbf. From there, the SO detail is listed in 2 separate tables. One contains info on the sale of the item, where the other table contains shipping info. When a sales order is created, the master record is created, and a detail record is created in both detail tables. This record contains the SONO and the line item number for the item being sold. From these 2 tables, I need to consolidate the line item data, into one area for the printing of price stickers. I can do this another way, but wanted to see if I could do this via a SELECT statement.
As another explanation,
SOMAST contains sono, custno
SOITEM contains sono, inumber, partno, desc
SORELS contains sono, inumber, invqty, unetprice, lshipdate
SOMAST.sono should join to SOITEM.sono and SORELS.sono
SOITEM.sono, SOITEM.inmuber should join to SORELS.sono, SORELS.inumber
Result should contain:
sono,custno,inumber,partno,desc,invqty,unetprice,lshipdate
Does that make better sense?
Previous
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only