| Author |
Topic |
|
RichardSteele
Posting Yak Master
160 Posts |
 Posted - 2007-12-26 : 21:52:03
|
| I'm trying to write a query that pulls together various tables to create an order history for each of our customers.Here is a list of tables1. cust (customer) - there is a separate record and custnums for each bill to and shipto address2. order (cms) - the billto and shipto order info based on custnum, date of the order, etc. Orders can have backorders and shipped 3. items (on the order) including backorders and shipped items4. invoice - the invoice data such as the date the item(s) were invoiced. invoices are made when the item is billed and shipped.5. box - the box information for each item on the order if the order was shipped. there can be more than one box per invoice. Here's what I've got so far...SELECT * ;a.custnum, a.firstname, a.lastname, a.company, ;a.addr, addr2, a.city, a.state, a.zipcode, a.country, ;s.desc, s.notation, ;i.inpart, i.item, i.quanto, i.quantf, i.quantb, i.quantp, i.quants, i.it_unlist, ;i.box, i.discount, i.picked, i.it_sdate, i.custominfo, i.item_state, i.quotation, ;i.popentry, i.ship_via, i.ship_to, i.ship_when, ;v.inpart, v.custnum, v.merch, v.shipping, v.amt_paid, v.stax, v.paymethod, v.amount, ;b.charges, b.ship_date, ;c.order FROM cust a ;INNER JOIN cms c ON a.custnum=c.custnum ;INNER JOIN invoice v ON v.order=c.order ;INNER JOIN items i ON i.order=v.order ;INNER JOIN box b ON b.order=v.order ;INNER JOIN stock s ON s.number=i.item ;However it is duplicating some data. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2007-12-27 : 03:07:16
|
| Can you specify what data is duplicating here? Also can you give some idea of desired o/p? May be give some sample data to illustrate it too.. |
 |
|
|
KenW
Constraint Violating Yak Guru
391 Posts |
Posted - 2007-12-28 : 16:02:09
|
I highly doubt that's what you've got now, as you have at least two different syntax errors in what you've shown:SELECT * ; -- no comma after *, ; is a statement terminator, a.company, ; -- again, ; is a statement terminator, and you've repeated it at the end of every line. -- The only valid use above is at the end of your statement following the last INNER JOIN. As visakh16 said, please post your DDL, some sample data, the actual code you've tried, and the results you're getting. Perhaps then someone can help. |
|
|
|
RichardSteele
Posting Yak Master
160 Posts |
Posted - 2007-12-28 : 22:10:53
|
| This is a foxpro convention that allows code to break to the next line making it easier to read (I cut and paste the foxpro code, sorry!) I have been able to fix the duplicate data I was having. I needed another field comparison in an inner join. I am now on to a different problem. |
|
|
|
|
|
|
Help with Query