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).
_______________________________________________
Post Messages to: ProFox@leafe.com
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/4b4e9d8b.7010...@gmail.com
** 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.
