Nested Query, Link two separate queries together on more than one field
Posted on 2013-11-27
I have two queries, the first pulls through all the Water charges from a view:-
select PlaceRef, Accountcode, StartDate, EndDate, NetAmt
WHERE AccountCode = 'WATER'
the second pulls through all the transactions from a two views:
select TransactionWeek, TransactionDate, TransactionAmount, AccountCode, PlaceRef, TenancyRef
INNER JOIN vwTenancyCurrent
ON vwTenancyTransactionsCurrent.TenancySystemRef = vwTenancyCurrent.TenancySystemRef
WHERE AccountCode='DEBIT' and TransactionWeek='1'
I would like to join both of these queries together on PlaceRef and StartDate from the first query, MATCHING the PlaceRef and TransactionDate from the second query.
Thanks in advance for your help!