Limiting the results on a matching table query

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top