This website uses cookies to ensure you get the best possible experience. See our Cookies Policy.

PMG Digital Made for Humans

SQL Join on One Column Contained in Another

2 MINUTE READ | March 14, 2017

SQL Join on One Column Contained in Another

SQL joins typically look to combine two tables into a set of columns that exist in both. Usually, the values match exactly, making the join straightforward. We can also use ILIKE to make the join case-insensitive and BETWEEEN if we have the date in one table and start date and end date in another.

Those don’t always get the job done on their own. Sometimes we need to be able to join one table to another if the value in the first table is contained in the value in the second. That’s probably clear as mud, so let me walk you through a practical example.

I typically use this technique when working with combined budgets. We typically don’t get our budgets down to the vendor level, we get one budget for a set of vendors, as seen below.

Our cost data, however, is broken out by vendor.

So how can we easily pull the cost data for each vendor in a given budget? Let’s backtrack for a second. How would we pull all budgets that contain the vendor ‘mediamath’?

We would leverage the wildcard symbol. Placing a wildcard at the start and end of the string finds all budget line items where the vendor’s column contains ‘mediamath’.

The difference for the join is that we have to apply the wildcard to the start and end of the vendor column in the cost data. We can do this by simply concatenating the wildcard to the start and end of the column name. This creates a dynamic string as opposed to the static ‘%mediamath%’ in the previous example.

Insights meet inbox

Sign up for weekly articles & resources.

Now we see our budget line items with all the cost right there, just as we wanted. It’s a pretty simple trick once you look at it, but extremely effective when you need it.


Posted by Preston Smith

Related Content

thumbnail image

Get Informed

PMG’s Predictive Dashboard Wins Innovation Award

1 MINUTE READ | September 28, 2021

Get Informed

The Road to Recovery for the Travel Industry

5 MINUTES READ | November 19, 2020

thumbnail image

Get Informed

Facebook Details Approach to Mobile Industry Changes

4 MINUTES READ | August 31, 2020

Get Inspired

Considerations for Reengaging New Online Customers

1 MINUTE READ | April 29, 2020

Get Informed

EMEA Search Trends Amid COVID-19

8 MINUTES READ | April 28, 2020

thumbnail image

Get Informed

A Permanent Shift Into Retail Media

1 MINUTE READ | April 23, 2020

Get Informed

Social eCommerce is The Darling of Cyber Weekend

4 MINUTES READ | December 2, 2019

thumbnail image

Get Informed

Reports of Amazon Clean Room Draws Attention of Advertisers

2 MINUTES READ | September 4, 2019

Get Informed

PMG Shares Thoughts About Rumored Amazon Data Clean Room

1 MINUTE READ | August 28, 2019

Get Inspired

Working with an Automation Mindset

5 MINUTES READ | August 22, 2019

ALL POSTS