I am pretty sure this can be acheived a few different ways but I was under the impression I could get this done by using a join.
I want to be able to find all the companies in the 'company' table by their corporateID but if the company has an entry with a specific kind of companyIDTypeID then I want to exclude those from the result set.
iCompanyID -- maps to the company in the company table
iCompanyIDTypeID -- maps to a mapping in another table for description
For example, I want to do the opposite of this:
Here I would be returning a result set that gave me all the companies that had that corporateID but that were also found in the CompanyID table with the TypeID criteria. I want to say give me all the companies with the corporateID but IF you find them in the COmpanyID table with the TypeID criteria exclude that company from the result set.
SELECT Company.iCompanyID FROM Company
LEFT JOIN CompanyID ON Company.iCompanyID = CompanyID.iCompanyID
WHERE Company.iCorporateCompanyID = $VariableCorporateID
AND CompanyID.iCompanyIDTypeID = $VariableIDTypeToken
I am asking because I thought this could be done with a join.