Skip to Content
0
Former Member
Jan 08, 2008 at 07:45 PM

Need to Filter by Records not in Separate Table

22 Views

Post Author: MCool

CA Forum: Formula

Hello

I'm puzzled as to how to create a report from a VFP database that will filter the results from three joined tables by only showing records from one field in one of the three joined tables that are not present in a field in a fourth table. The current SQL query is below.

SELECT `inventor`.`part_no`, `inventor`.`revision`, `inventor`.`part_sourc`, `inventor`.`status`, `invtmfgr`.`instore`, `warehous`.`warehouse`, `invtmfgr`.`location`, `invtmfgr`.`qty_oh`, `inventor`.`descript` FROM (`inventor` `inventor` INNER JOIN `invtmfgr` `invtmfgr` ON `inventor`.`uniq_key`=`invtmfgr`.`uniq_key`) INNER JOIN `warehous` `warehous` ON `invtmfgr`.`uniqwh`=`warehous`.`uniqwh` WHERE `inventor`.`status`='Active'

I want to filter the output so I only get records where the values in inventor.uniq_key are not present in bom_det.uniq_key. The bom_det table is included in the report but not linked.

I'm using Crystal Reports 9.

Thanks in advance.

Mike