F
Frank
Hi,
I'm trying to match results within a table so that if there is a 1 for 1
match between 2 quantities, those 2 records will show up and not any other
that might also match these two. This is proving to be more difficult than
expected. I've tried many incarnations of queries and subqueries and at least
the one below returns a table that I might be able to limit maybe with a
subquery. If I could limit each if the primarykey values on both sides of the
resulting table to unique values that might get me closer. Any suggestions.
Thanks
SELECT
a.pkey, a.clntrade2, a.total, a.src, a.ridscrub,
max(b.pkey), b.clntrade2, b.total,b.src
FROM [TblSCRUB] AS a INNER JOIN [TblSCRUB] AS b
ON
(a.clntrade2=b.clntrade2) AND
(ABS(a.total+b.total)<=50) and
(a.src<>b.src)
GROUP BY
a.pkey, a.clntrade2, a.total, a.src, a.ridscrub, b.clntrade2, b.total,b.src
ORDER BY Max(b.pkey) DESC;
Output (problem is the repeating values in the b.pkey column)
pkey a.clntrade2 a.total a.src (b.pkey)b.clntrade2 b.total b.src
7061 547 1531 PALS 3025 547 -1531 ITS
7059 547 1531 PALS 3025 547 -1531 ITS
3024 547 -1531 ITS 7061 547 1531 PALS
3025 547 -1531 ITS 7061 547 1531 PALS
I'm trying to match results within a table so that if there is a 1 for 1
match between 2 quantities, those 2 records will show up and not any other
that might also match these two. This is proving to be more difficult than
expected. I've tried many incarnations of queries and subqueries and at least
the one below returns a table that I might be able to limit maybe with a
subquery. If I could limit each if the primarykey values on both sides of the
resulting table to unique values that might get me closer. Any suggestions.
Thanks
SELECT
a.pkey, a.clntrade2, a.total, a.src, a.ridscrub,
max(b.pkey), b.clntrade2, b.total,b.src
FROM [TblSCRUB] AS a INNER JOIN [TblSCRUB] AS b
ON
(a.clntrade2=b.clntrade2) AND
(ABS(a.total+b.total)<=50) and
(a.src<>b.src)
GROUP BY
a.pkey, a.clntrade2, a.total, a.src, a.ridscrub, b.clntrade2, b.total,b.src
ORDER BY Max(b.pkey) DESC;
Output (problem is the repeating values in the b.pkey column)
pkey a.clntrade2 a.total a.src (b.pkey)b.clntrade2 b.total b.src
7061 547 1531 PALS 3025 547 -1531 ITS
7059 547 1531 PALS 3025 547 -1531 ITS
3024 547 -1531 ITS 7061 547 1531 PALS
3025 547 -1531 ITS 7061 547 1531 PALS