SQL: Inner Join Works, Left Join Does Not

[PeteCresswell]

When I Inner Join to a subquery, records are returned from it.

But when I Left Join to the same query, there are no hits on the join.

Here is the common query that is joined to in both cases:
-----------------------------------------------------------------------------------
SELECT tblTradingAccountStrategy.TradingAccountID, First
(tblTradingAccountStrategy.StrategyID) AS FirstOfStrategyID
FROM tblTradingAccountStrategy
GROUP BY tblTradingAccountStrategy.TradingAccountID;
-----------------------------------------------------------------------------------

Here is the join that returns records:
-----------------------------------------------------------------------------------
SELECT tblTradingAccountStrategy.TradingAccountID, First
(tblTradingAccountStrategy.StrategyID) AS FirstOfStrategyID
FROM tblTradingAccountStrategy
GROUP BY tblTradingAccountStrategy.TradingAccountID;
-----------------------------------------------------------------------------------

Here is the join that *seems* like it should return records,
but does not:
-----------------------------------------------------------------------------------
SELECT tblTradingAccount.TradingAccountID,
qryAdmin_TradingAccount_Load_TradingAccounts_Strategy.FirstOfStrategyID
FROM tblTradingAccount LEFT JOIN
qryAdmin_TradingAccount_Load_TradingAccounts_Strategy ON
tblTradingAccount.TradingAccountID =
qryAdmin_TradingAccount_Load_TradingAccounts_Strategy.TradingAccountID;
-----------------------------------------------------------------------------------

This smells of Yours Truly doing something really simple/dumb, but for
the life of me I cannot see what.

??

 

[Dale Fye]

My first thought is to replace the First( ) with Min( ). Since you have no
Order By clause in your query, the records will normally be returned in the
order defined by the PK. But if StrategyID is a foreign key, there is no way
to make sure that First( ) will return the smallest value, or the ID
associated with the earliest (temporaly) record.

Second, the query you define as the Join that returns records does not even
have a JOIN clause in it (in actuality, it is the same as the query above it).

Post back with the INNER JOIN SQL that works and I'll try to figure out what
is going on. At first glance, I don't see anything wrong with the LEFT JOIN
query.