NCCS offers a variety of methods for selecting (filtering) records for custom reporting and
analysis. User-defined Custom Lists, for example, allow a user to create and store personal lists
and 'tag' or 'untag' the organizations that they want to include in each list. Each Custom List is
unique, but an organization can be 'tagged' to any number of Custom Lists. Custom Lists can be
combined with other Custom Lists or used in conjunction with other selection criteria, for example:
NCCS is producing a report from the Core 2006 PC file that includes international organizations
(defined as Ntee code = Q) as well as some organizations that are primarily domestic (Ntee code <>
Q), but have been 'tagged' to one or more user-defined Custom Lists due to their international work.
In this example, the query selects records from the Core 2006 PC file that are either coded as
International (NteeFinal like Q%) or are tagged for one or more 'international' user-defined Custom
Lists. The ‘tag’ file (nccs.UserCustomLists) contains a record for each tag in each Custom List and
an EIN may be tagged to more than one Custom List, so ‘group by’ is used within a subquery to
eliminate any potential duplicate EINs before joining to the core2006pc file (creating a one-to-one
match between the Core file and the desired list of EINs):
SELECT y.ListID, y.NteeFinal, x.* FROM nccs.core2006pc x,
(SELECT Q.ein, tagIN.ListID, Q.NteeFinal FROM nccs.nteedocAllEins Q left join
nccs.userCustomLists tagIN
ON Q.ein=tagIN.ein WHERE Q.NteeFinal like 'Q%' or tagIN.ListID in (1, 2, 7, 14) group by
Q.ein) y
WHERE x.EIN=y.EIN;
Here is more info on subqueries from the online MySQL documentation:
http://dev.mysql.com/doc/refman/5.0/en/subqueries.html