Find all instances where members overlap campaigns

Question

I have a table with memberID and 10 campaigns as columns, each with a 1, 0 identifying if the member is part of that campaign. Members can be in more than one campaign.

Is there a SQL query to find all instances where members in campaign1 are in campaign 2, all members in campaign 1 are in also in campaign 2 and 3 etc.. without having to write 10! cross joins....

Patient_AGN|MCP|P5E|CMD|KRG|EMP|CAR|SEG|CON|EMB|HP
1   |1| 0   |0| 0|  0|  0|  0|  0|  0|  0|
2   |1| 0   |0| 0|  0|  0|  0|  1|  0|  0|
3   |0| 0   |1| 0|  0|  0|  0|  0|  0|  0|
4   |0| 0   |0| 0|  0|  1|  1|  0|  0|  0|


  

Looked into cross joins but I would need to write a cross join 10! times

Answer 1

The problem of so many cross joins is caused by have one column per campaign, whereas it would be far more efficient to have one campaign column that identifies each campaign. Recently I saw this described like this: Your current table layout is good for humans but bad for databases.

So, the current table layout can be called "pivotted" and what we need to do is "unpivot" it so we have just 2 columns. Once we do this answering the question like who was in campaign X and Y become so much simpler. e.g:

CREATE TABLE mytable (
    Patient_AGN INT,    MCP INT,    P5E INT,    CMD INT,    KRG INT,
    EMP INT,    CAR INT,    SEG INT,    CON INT,    EMB INT,    HP INT
);

INSERT INTO mytable (Patient_AGN, MCP, P5E, CMD, KRG, EMP, CAR, SEG, CON, EMB, HP)
VALUES 
    (1, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0),
    (2, 1, 0, 0, 0, 0, 0, 0, 1, 0, 0),
    (3, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0),
    (4, 0, 0, 0, 0, 0, 1, 1, 0, 0, 0);

The following query "unpivots" your data into 2 columns. I chose to do this as a view but in truth you would be better off altering how you store the data into this 2 column style.

CREATE VIEW myview AS
SELECT Patient_AGN, 'MCP' AS campaign FROM mytable WHERE MCP = 1
UNION ALL
SELECT Patient_AGN, 'P5E' AS campaign FROM mytable WHERE P5E = 1
UNION ALL
SELECT Patient_AGN, 'CMD' AS campaign FROM mytable WHERE CMD = 1
UNION ALL
SELECT Patient_AGN, 'KRG' AS campaign FROM mytable WHERE KRG = 1
UNION ALL
SELECT Patient_AGN, 'EMP' AS campaign FROM mytable WHERE EMP = 1
UNION ALL
SELECT Patient_AGN, 'CAR' AS campaign FROM mytable WHERE CAR = 1
UNION ALL
SELECT Patient_AGN, 'SEG' AS campaign FROM mytable WHERE SEG = 1
UNION ALL
SELECT Patient_AGN, 'CON' AS campaign FROM mytable WHERE CON = 1
UNION ALL
SELECT Patient_AGN, 'EMB' AS campaign FROM mytable WHERE EMB = 1
UNION ALL
SELECT Patient_AGN, 'HP' AS campaign FROM mytable WHERE HP = 1;


select 
       Patient_AGN
     , campaign
from myview
order by
       Patient_AGN
     , campaign

So now the data looks like this:

Patient_AGN	campaign
1	MCP
2	CON
2	MCP
3	CMD
4	CAR
4	SEG

and then we can use rather simple queries to locate folks who participated in 2 specific campaigns (as an example):

select Patient_AGN
from myview
where campaign IN('CON','MCP')
group by Patient_AGN
having count(distinct campaign) = 2

Patient_AGN
2

fiddle