SQL Joins + Stuff in MEME

Basics of SQL for MEME Queries

Creatives and Campaigns

SELECT 
    * 
FROM 
    creatives;

Creatives
id	title	campaign_id
1	Skrillex Bees!	1
2	Cooking With Skrillex	1
3	Cooking With LotR Pwniez	2

SELECT 
    * 
FROM 
    campaigns;

Campaigns
id	name	Entry Date
1	Skrillex	29/1/13
2	Pwniez	1/3/13
3	Cats	4/7/12

Everything in both tables

SELECT
     * 
FROM 
    campaigns, creatives;


id	title	campaign_id	id_1	name	Entry Date
1	Skrillex Bees!	1 •	• 1	Skrillex	29/1/13
2	Cooking With Skrillex	1 •	• 1	Skrillex	29/1/13
3	Cooking With LotR Pwniez	2	• 1	Skrillex	29/1/13
1	Skrillex Bees!	1 •	2	Pwniez	1/3/13
2	Cooking With Skrillex	1 •	2	Pwniez	1/3/13
3	Cooking With LotR Pwniez	2	2	Pwniez	1/3/13
1	Skrillex Bees!	1 •	3	Cats	4/7/12
2	Cooking With Skrillex	1 •	4	Cats	4/7/12
3	Cooking With LotR Pwniez	2	4	Cats	4/7/12

Join on Matching Foreign Key

SELECT 
    * 
FROM 
    campaigns, creatives 
WHERE 
    creatives.campaign_id = campaigns.id;


id	title	campaign_id	id_1	name	Entry Date
1	Skrillex Bees!	1 •	• 1	Skrillex	29/1/13
2	Cooking With Skrillex	1 •	• 1	Skrillex	29/1/13
3	Cooking With LotR Pwniez	2	• 1	Skrillex	29/1/13
1	Skrillex Bees!	1 •	2	Pwniez	1/3/13
2	Cooking With Skrillex	1 •	2	Pwniez	1/3/13
3	Cooking With LotR Pwniez	2	2	Pwniez	1/3/13
1	Skrillex Bees!	1 •	3	Cats	4/7/12
2	Cooking With Skrillex	1 •	4	Cats	4/7/12
3	Cooking With LotR Pwniez	2	4	Cats	4/7/12


id	title	campaign_id	id_1	name	Entry Date
1	Skrillex Bees!	1 •	• 1	Skrillex	29/1/13
2	Cooking With Skrillex	1 •	• 1	Skrillex	29/1/13
3	Cooking With LotR Pwniez	2	2	Pwniez	1/3/13

restrict the columns

SELECT 
    id, 
    title, 
    name, 
    entry_date 
FROM 
    campaigns, 
    creatives 
WHERE 
    creatives.campaign_id = campaigns.id;


id	title	campaign_id	id_1	name	Entry Date
1	Skrillex Bees!	1 •	• 1	Skrillex	29/1/13
2	Cooking With Skrillex	1 •	• 1	Skrillex	29/1/13
3	Cooking With LotR Pwniez	2	2	Pwniez	1/3/13


id	title	name	Entry Date
1	Skrillex Bees!	Skrillex	29/1/13
2	Cooking With Skrillex	Skrillex	29/1/13
3	Cooking With LotR Pwniez	Pwniez	1/3/13

only Titles that Start With Cooking

    SELECT 
        id,
        title, 
        name, 
        entry_date 
    FROM 
        campaigns, 
        creatives 
    WHERE 
        creatives.campaign_id = campaigns.id  
    AND 
        title ILIKE 'Cooking%;


id	title	name	Entry Date
1	Skrillex Bees!	Skrillex	29/1/13
2	Cooking With Skrillex	Skrillex	29/1/13
3	Cooking With LotR Pwniez	Pwniez	1/3/13


id	title	name	Entry Date
2	Cooking With Skrillex	Skrillex	29/1/13
3	Cooking With LotR Pwniez	Pwniez	1/3/13

only creatives Added in Last 24 Hours

SELECT 
    id, 
    title, 
    name, 
    entry_date 
FROM 
    campaigns, 
    creatives 
WHERE 
    creatives.campaign_id = campaigns.id 
AND 
    entry_date >= now() - interval '24 hours';


id	title	name	Entry Date
1	Skrillex Bees!	Skrillex	29/1/13
2	Cooking With Skrillex	Skrillex	29/1/13
3	Cooking With LotR Pwniez	Pwniez	1/3/13


id	title	name	Entry Date
3	Cooking With LotR Pwniez	Pwniez	1/3/13

Now With Added OOP-Goodness

Now With Added OOP-Goodness

SELECT * FROM creatives;

Creatives
id	title	discriminator	campaign_id
1	Skrillex Bees!	ViralCreative	1 •
2	Cooking With Skrillex	EngagementCreative	1 •
3	Cooking With LotR Pwniez	ViralCreative	2 •

SELECT * FROM campaigns;

Campaigns
id	name	Entry Date
• 1	Skrillex	29/1/13
• 2	Pwniez	1/3/13
3	Cats	4/7/12

only Viral Creatives

SELECT 
    id, 
    title, 
    name,
    discriminator, 
    entry_date 
FROM 
    campaigns, 
    creatives 
WHERE 
    creatives.campaign_id = campaigns.id 
AND 
    discriminator = 'ViralCreative';


id	title	discriminator	name	Entry Date
1	Skrillex Bees!	ViralCreative	Skrillex	29/1/13
2	Cooking With Skrillex	EngagementCreative	Skrillex	29/1/13
3	Cooking With LotR Pwniez	ViralCreative	Pwniez	1/3/13


id	title	discriminator	name	Entry Date
1	Skrillex Bees!	ViralCreative	Skrillex	29/1/13
3	Cooking With LotR Pwniez	ViralCreative	Pwniez	1/3/13