Join table to show records without match 1

[apex82]

I have the following tables:

System:

System_ID | System Code | System Description | BusinessID |

Business:

Business_ID | Business Code | Business Description | ModuleID

The join here would be between the System.BusinessID and the Business.Business_ID.

On my page I initially had a dropdown menu where I was selecting all from the Business table:

str_SQL = "SELECT * FROM [Business] ORDER BY [Business Code] ASC;"

Basically I want to change this SQL statement so I show the records in the Business table that don’t have a match on the BusinessID in the System table.

Does anyone know how I would change my SQL statement to do this?

Thanks.

 

[r937]

SELECT Business.* 
  FROM Business 
LEFT OUTER
  JOIN System
    ON System.BusinessID = Business.BusinessID  
 WHERE System.BusinessID IS NULL
ORDER 
    BY Business.[Business Code] ASC

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[jbenson001]

Select [columns]
From Business b
Left Join System s ON b.BusinessID = s.BusinessID
Where b.BusinessID IS NULL

 

[jbenson001]

Sorry, should be WHERE s.BusinessID IS NULL

 

[apex82]

Thanks, that is now working.