Search code examples
sqlcrystal-reportscrystal-reports-2010

Suppress records from one table that are also in another table

List1 is my main table and List2 is a secondary table. Is there a way to display people from List1 that are not on List2? Or suppress if they are on List2?

The common field is personID.

List1:  
name;id           
Ed Newb;1  
John Law;2  
Mike Jordan;3  

List2:  
name;id   
Ed Newb; 1  
Mike Jordan; 3
Other Guy; 4

I am seeking a query that will remove data on list2 from list1:

Report:  
List1.name;List1.id   
John Law; 2
Solution

  • Looks like you can filter them out in SQL:

    SELECT
  l1.id,
  l1.name
FROM 
  list1 l1
  LEFT OUTER JOIN list2 l2 ON l1.id = l2.id
WHERE
  l2.id IS NULL