I have a query I want to run as a subquery that will return a set of FK's. With them I want to return only rows that has a matching key.
SELECT ID FROM tblTenantTransCode WHERE tblTenantTransCode.CheckbookCode = (SELECT ID FROM tblCheckbookCode WHERE Description = 'Rent Income')
That will return all the transaction codes that have a checkbook code that matches Rent Income
Now I want to select All Transactions where their transactioncode matches an ID returned in the subquery. I've gotten this far, but SQL Server complains of a syntax error. How can I do this?
SELECT * FROM tblTransaction WHERE tblTransaction.TransactionCode IN (SELECT ID FROM tblTenantTransCode WHERE tblTenantTransCode.CheckbookCode = (SELECT ID FROM tblCheckbookCode WHERE Description = 'Rent Income'))
tblCheckbookCode ID Description Other Info tblTenantTransCode ID CheckbookCode <-- fk we're looking for in the tblCheckbookCode. We're selecting only checkbook codes that have the Description 'Rent Income' Other Info tblTransactions ID TransactionCode <-- fk to tenant transaction code. We're looking for an ID that is returned in the above query/join