Companies Power Search Queries

A. List of Companies Power Search Names

These are BASIC queries or low level building blocks designed and managed in the Deskflow Administrator utility.

These building blocks are for use within the Companies Power Search utility.

Most of the queries are parameter driven, with the User defining the WHERE clause information at run time.

Queries that have a partial WHERE clause require the User to enter parameters.

Queries that have a fully formed WHERE clause are stand-alone SQL queries.

B.  Companies Power Search Queries

Companies by Location Phone (Phone 1)

 SELECT CompaniesID FROM Addresses  WITH (NOLOCK)   WHERE Addresses.Phone1

 Companies Without Industries

 SELECT C.CompaniesID

 FROM Companies C WITH (NOLOCK)

 LEFT JOIN CompaniesIndustry CI WITH (NOLOCK)

 ON C.CompaniesID = CI.CompaniesID

 WHERE CI.CompanyIndustriesID IS NULL

 Company by Alias Name

 SELECT CompaniesID FROM CompaniesAliases  WITH (NOLOCK)  WHERE Name

 Company by Branch

 SELECT CompaniesID FROM Companies  WITH (NOLOCK)   WHERE BranchName

 Company by City

 SELECT CompaniesID FROM Addresses   WITH (NOLOCK)

 WHERE CompaniesID >0 AND Addresses.City

 Company by Location FAX (Phone 2)

 SELECT CompaniesID FROM Addresses  WITH (NOLOCK)   WHERE Addresses.Phone2

 Company by Location Type

SELECT CompaniesID FROM Addresses  WITH (NOLOCK)  

JOIN LocationType  WITH (NOLOCK)  

ON Addresses.LocationType = LocationType.Name

WHERE LocationType.Description

 Company by Marketing Status

 SELECT CompaniesID FROM Companies  WITH (NOLOCK)   WHERE OpportunityStatus

 Company by Name

 SELECT CompaniesID FROM Companies  WITH (NOLOCK)   WHERE Company

 Company by Name or Known As

 SELECT CompaniesAliases.CompaniesID

 FROM CompaniesAliases  WITH (NOLOCK)

 WHERE CompaniesAliases.Name

 Company by Notes

 SELECT CompaniesID FROM Notes WITH (NOLOCK)  WHERE CONTAINS

 Company by Parent Name

 SELECT A.CompaniesID FROM Companies  AS A WITH (NOLOCK)

 LEFT JOIN Companies AS B WITH (NOLOCK)

 ON A.ParentCompanyID=B.CompaniesID

 WHERE B.Company

 Company by PostalCode

 SELECT CompaniesID FROM Addresses   WITH (NOLOCK)

  WHERE  CompaniesID >0 AND PostalCode

 Company by Product

 SELECT CompaniesID FROM Companies  WITH (NOLOCK)   WHERE Products

 Company by Province/State

 SELECT CompaniesID FROM Addresses   WITH (NOLOCK)

  WHERE  CompaniesID >0 AND Province

 Company has Person

 SELECT Companies.CompaniesID FROM Companies  WITH (NOLOCK)

 LEFT JOIN Positions WITH (NOLOCK)

 ON Positions.CompaniesID =   Companies.CompaniesID

 LEFT JOIN People WITH (NOLOCK)

 ON People.PeopleId = Positions.PeopleID

 WHERE People.LastName

 Company in Target List for Project Code

 SELECT ProjectsCompaniesLists.CompaniesID

 FROM ProjectsCompaniesLists WITH (NOLOCK)

 JOIN Projects WITH (NOLOCK)

 ON (ProjectsCompaniesLists.ProjectsID = Projects.ProjectsID)

 WHERE Projects.JobCode

 Company is in a List

 SELECT CompaniesID FROM Companies

 JOIN ListsDetails WITH (NOLOCK)

 JOIN Lists WITH (NOLOCK)

 ON ( ListsDetails.ListID = Lists.ListsID AND Lists.SourceTable = 'Companies')

 ON ( ListsDetails.RecordID = Companies.CompaniesID)

 WHERE Lists.Name

 Company Main Phone

 SELECT CompaniesID FROM Companies  WITH (NOLOCK)   WHERE Companies.Switchboard

 Company with multiple contacts

 SELECT CompaniesID FROM Companies WITH (NOLOCK)

 WHERE CompaniesID IN

 (SELECT CompaniesID FROM Positions WITH (NOLOCK) GROUP BY CompaniesID having count(*) >1 )

 Company with only one Contact

 SELECT CompaniesID FROM Companies WITH (NOLOCK)

 WHERE CompaniesID IN

 (SELECT CompaniesID FROM Positions WITH (NOLOCK) GROUP BY CompaniesID having count(*) =1)

 Search in CustomField5

 SELECT CompaniesID FROM Companies WITH (NOLOCK)

 WHERE CustomText5