this question doesn't make sense on multiple fronts:
First, you provided no DDL, making things harder. We don't know the data type of amount
Second, your query does not even run with your sample. It references Book.store, but you don't select from a table named book, and you don't create an alias, so thats obviously not a real query.
Third, you are doing both a group by and a distinct, which is redundant. Suspenders and a belt ...
Fourth, what real world problem would have you joining on amount like this? Doesn't make much sense.
Fifth, I tried to build sample DDL and copy your query with fixed aliases, and did not get the results (duplicates) that you claimed you are getting.
So my guess is you had a problem on much more complicated tables you did not choose to share, so tried to boil down the problem into an abstract example, but did not put any serious effort or even a test into seeing whether the example demonstrated the problem.
briancampbellmcad (1/17/2014)
I'm struggling with what would seem quite easy...I have two tables tblBank and tblBook... I'm getting duplicates... any clues?
Looks like the LEFT JOIN would give me only 2 rows back:
SELECT DISTINCT Book.Store, Book.Amount
FROM tblBank LEFT JOIN tblBook ON tblBank.Amount = tblBook.Amount
GROUP BY Store, Amount;
tblBank
Store Amount
15 400.00
21 900.00
27 300.00
29 500.00
tblBook
Store Amount
8 300.00
15 400.00
15 400.00
21 600.00
27 300.00
27 300.00
29 700.00
30 100.00
Query results:
Store Amount
15 400.00
15 400.00
27 300.00
27 300.00