Query question

[HedgeKAM]

Hi,

I have two tables that look like this:

Transactions
-------------
TransID
ClientID
SalesID
FundID

Funds
-------------
FundID
FundName

a Client has many transactions in many Funds. Each transaction has a SalesID. I want to select distinctly what Funds (FundID) a Sales persons (SalesID) Client (ClientID) has.

Ex:
SalesID 1 has 2 clients:
Client1 has 6 transactions, 3 in FundID 1 and 3 in FundID 2.
Client 2 has 3 transactions, all in FundID3.

I want to query what funds SalesID1's Client1 has. That would then return:
FundID 1
FundID 2

Any help would be greatly appreciated!

 

[FoFa]

Fingers are not listening this morning

Trythis:
SELECT T.ClientID, T.SalesID, F.FundID
FROM Transactions T
Inner Join Funds F ON F.FundID = T.FundID
Where T.ClientID = 1 and T.SalesID = 1
(for your example)
However if you wanted to group by instead of selecting a specific you could replace the WHERE clause with or use different criteria and add to with the following;
GROUP BY T.ClientID, T.SalesID

 

[HedgeKAM]

Thanks for your response! I tried your SQL but it isnt selecting the funds distinctly. Im getting several of the same FundID

 

[Pat Hartman]

Select ClientID, SalesID, FundID, Count(*) As TranCount
From Transactions
Group By ClientID, SalesID, FundID;