Knowledgebase

User Custom Lists - How to select data with MySQL using Tags in a subquery


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


Added 12/02/2008 by jdurnford, Modified 11/11/2009 by tpollak

Comments

No comments.

Please login to add your own comments.