Hi
Looked at the code that Frank supplied and could see a potential
problem. As my website programmer was here I asked if he could see the
problem also.
Any way he came up with the code below.
If you have duplicate entries for one stock code (i.e. if the following
items are added again in addition to below, note how "paper"
re-appears):
INSERT INTO Stock (cCode, cDesc) VALUES ("100", "book")
INSERT INTO Stock (cCode, cDesc) VALUES ("100", "+book")
INSERT INTO Stock (cCode, cDesc) VALUES ("101", "paper")
You _may_ want to filter the results down further with a couple of
DISTINCT selects:
SELECT DISTINCT Stock.cCode, Stock.cDesc ; && filter out duplicate
records (prevent items appearing twice)
FROM Stock ;
INNER JOIN (SELECT cCode, COUNT(cCode) as Dups ;
FROM (SELECT DISTINCT cCode, cDesc FROM Stock) AS
Filtered ; && filter out duplicates (descriptions only)
GROUP BY cCode ;
HAVING Dups > 1) DupCodes ON Stock.cCode =
DupCodes.cCode
Or count the duplicate records...
SELECT Stock.cCode, Stock.cDesc, COUNT(Stock.cCode) as Total ; && count
duplicate records (prevent items appearing twice)
FROM Stock ;
INNER JOIN (SELECT cCode, COUNT(cCode) as Dups ;
FROM (SELECT DISTINCT cCode, cDesc FROM Stock) AS
Filtered ; && filter out duplicates (descriptions only)
GROUP BY cCode ;
HAVING Dups > 1) DupCodes ON Stock.cCode =
DupCodes.cCode ;
GROUP BY Stock.cCode, Stock.cDesc
Cheers
Peter
Peter Hart Computers.
On Behalf Of Frank Cazabon
Sent: 12 September 2009 13:46
Subject: Re: Need help on "Select" command
This might be what you are looking for, but I have not fully tested it:
CREATE CURSOR Stock;
(cCode c(3),;
cDesc c(20))
INSERT INTO Stock (cCode, cDesc) VALUES ("100", "book")
INSERT INTO Stock (cCode, cDesc) VALUES ("100", "+book")
INSERT INTO Stock (cCode, cDesc) VALUES ("101", "paper")
INSERT INTO Stock (cCode, cDesc) VALUES ("102", "pen")
INSERT INTO Stock (cCode, cDesc) VALUES ("102", "+pen")
INSERT INTO Stock (cCode, cDesc) VALUES ("103", "stamp")
INSERT INTO Stock (cCode, cDesc) VALUES ("104", "brush")
SELECT Stock.cCode, Stock.cDesc ;
FROM Stock ;
INNER JOIN (SELECT cCode, COUNT(cCode) as Dups ;
FROM stock ;
GROUP BY cCode ;
HAVING Dups > 1) DupCodes ON Stock.cCode =
DupCodes.cCode
free...@sohocare wrote:
> dear all GURU,
>
> Can anyone help me on how to use "SELECT * from stock .." command to
get the following result?
>
> STOCK.DBF
> code description
> 100 book
> 100 +book
> 101 paper
> 102 pen
> 102 +pen
> 103 stamp
> 104 brush
>
>
> RESULT: (List of duplicate code)
> code description
> 100 book
> 100 +book
> 102 pen
> 102 +pen
_______________________________________________
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/a57fa4cf19531343a2ee11b57db8e3af04c...@server.peterhartcomputers.local
** 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.
