Thanks for the input Ricardo. for the archive, it turned out that I had four records in the glentrdt file with the same glseq. After I updated my where clause to include "and glentrdt.glaccnt = '60120- ' I didnt get the duplicates any more.
2010/1/13 Ricardo Aráoz <[email protected]>: > Jeff Roberts wrote: >> I have been working on a sql statement all afternoon and it is >> starting to make me seriously doubt my abilities. I have a table, >> glentrdth which has some records I from which need to get records out >> for period 13 year 2009. However, this table only contains a glseq >> field which should correspond to records in another table called >> glentrdt which have the period, year, and glseq records in them. I >> also need to match these records with records in a table called >> armast to get a field called warehouse matching these records up based >> on invno value of both tables. Now this seems easy to me however >> every syntax I've tried yields four records for every one record I >> need. I know this may not make a lot of sense without access to the >> data but I'd really appreciate it if anyone would look at the sql >> statement below and tell me if anything obviously wrong jumps out at >> them: >> >> SELECT glentrdth.glaccnt, glentrdth.glseq, glentrdth.glamount, >> glentrdth.invno, glentrdth.custno, glentrdt.year,glentrdt.period, >> armast.warehouse ; >> FROM t:\visprod\data06\glentrdth, t:\visprod\data06\armast, >> t:\visprod\data06\glentrdt ; >> WHERE glentrdth.invno = armast.invno AND glentrdth.glseq = >> glentrdt.glseq AND glentrdt.year = '2009' AND glentrdt.period = '13' >> AND glentrdth.glaccnt = '60120- ' ; >> INTO CURSOR junk >> >> this last iteration is going back to fox 2.6 syntax but I am working >> in vfp 9 so I'm not opposed to the newer join syntax it just wasnt >> getting me any better results. any help would be appreciated. >> -Jeff >> > > > SELECT glentrdth.glaccnt, glentrdth.glseq, glentrdth.glamount,; > glentrdth.invno, glentrdth.custno, glentrdt.year,; > glentrdt.period,armast.warehouse ; > FROM t:\visprod\data06\glentrdth; > full join t:\visprod\data06\armast; > on glentrdth.invno = armast.invno; > full join t:\visprod\data06\glentrdt ; > on glentrdth.glseq = glentrdt.glseq ; > WHERE glentrdt.year = '2009' AND glentrdt.period = '13' > AND glentrdth.glaccnt = '60120- ' ; > INTO CURSOR junk > > I think this would be your select in a more explicit idiom. Now I'd > rather use left joins instead of full joins (specially if you are > getting extra records). The other reason to get extra records is that > there is a one to many relationship between the tables. > Try this (untested) : > > SELECT 'glentrdth' as table1, glentrdth.*, ; > 'armast' as table2, armast.*, ; > 'glentrdt' as table3, glentrdt.*, ; > FROM t:\visprod\data06\glentrdth; > full join t:\visprod\data06\armast; > on glentrdth.invno = armast.invno; > full join t:\visprod\data06\glentrdt ; > on glentrdth.glseq = glentrdt.glseq ; > WHERE glentrdth.invno in (; > SELECT dth.invno ; > FROM t:\visprod\data06\glentrdth as dth; > full join t:\visprod\data06\armast as ast; > on dth.invno = ast.invno; > full join t:\visprod\data06\glentrdt rdt ; > on dth.glseq = rdt.glseq ; > GROUP BY dth.invno; > WHERE rdt.year = '2009' AND rdt.period = '13'; > AND dth.glaccnt = '60120- ' AND count(*) > 1; > ) ; > INTO CURSOR junk > > This should (if it does what I think it does) list only the duplicate > invno's. Then you can check which is the culprit table for the duplicate > records (the one that has different field values in two duplicate records). > > > > [excessive quoting removed by server] _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[email protected] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
