#1
  1. Recovering Intellectual
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2006
    Location
    Orange County, CA
    Posts
    1,306
    Rep Power
    785

    Using JOIN to omit a set of records


    Hello,

    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.
    Code:
    Table 'Company'
    iCompanyID
    iCorporateCompanyID
    
    Table 'CompanyID'
    iCompanyID           -- maps to the company in the company table
    iCompanyIDTypeID  -- maps to a mapping in another table for description
    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.

    For example, I want to do the opposite of this:

    Code:
    SELECT Company.iCompanyID FROM Company 
    LEFT JOIN CompanyID ON Company.iCompanyID = CompanyID.iCompanyID
    WHERE Company.iCorporateCompanyID = $VariableCorporateID
    AND CompanyID.iCompanyIDTypeID = $VariableIDTypeToken
    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.

    I am asking because I thought this could be done with a join.
    Bugs that go away by themselves come back by themselves
    Beware - your loyalty will not be rewarded
  2. #2
  3. Recovering Intellectual
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2006
    Location
    Orange County, CA
    Posts
    1,306
    Rep Power
    785
    Ok the answer was simple - I needed to restrict the join'd data further and then simply select where the row in the exluded database was null:

    Code:
    SELECT Company.iCompanyID FROM Company 
      LEFT JOIN CompanyID ON Company.iCompanyID = CompanyID.iCompanyID
        AND CompanyID.iCompanyIDTypeID = $VariableIDToken
       WHERE CompanyID.iCompanyID IS NULL 
       AND Company.iCorporateCompanyID = $VariableCorporateID
    I apologize for that I probably should have kept at it a little longer before posting here - at least we have something on the forum now though in case it helps someone else.
    Bugs that go away by themselves come back by themselves
    Beware - your loyalty will not be rewarded

IMN logo majestic logo threadwatch logo seochat tools logo