Show machines not active in a many-to-many relationship

[jmar]

Hi,

I have a portal that needs to display machines not active on any maintenance plan in a many-to-many relationship. The tables are (I did not include all fields, as they are not needed):

tblMachines:

MachineID

Serialnumber

ProductID

....

tblMaintenanceContract:

MaintenanceContractID

Active

EndDate

....

tblMachinesMaintenanceContract:

Identifier

MachineID

MaintenanceContractID

Active

....

tblMaintenanceContract can hold many machines from tblMachines. A machine can only be active on 1 maintenance contract on any given time. A maintenance contract that are not active can not hold any active machines.

In the portal I need to show the machine list of machines that are not active on any maintenance contracts.

To the purpose I created the following SQLstring:

SELECT MachineID FROM tblMachines

LEFT JOIN tblMachinesMaintenanceContract ON tblMachines.MachineID=tblMachinesMaintenanceContract.MachineID

WHERE Active='No' AND tblMachines.MachineID NOT IN (SELECT MachineID FROM tblMachines LEFT JOIN tblMachinesMaintenanceContract ON tblMachines.MachineID=tblMachinesMaintenanceContract.MachineID WHERE Active='Yes')

I know for a fact that above SQLstring will display the correct results (testet with SQLBuilder).

I am then trying to use the SQLstring in the filter of portal records but the result is ... nothing.... How can I get the portal to show the results?

Thanks!