• About Us
    • New York
  • Work
  • Capabilities
  • Careers
  • Technology
  • Blog
  • Contact Us
  • About Us
    • New York
  • Work
  • Capabilities
  • Careers
  • Technology
  • Blog
  • Contact Us
March 14, 2017

SQL Join on One Column Contained in Another

Posted by Preston Smith

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.

budgetdata

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

costdata

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’?

budgetmediamath

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.

finalcode

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.

SQL
Previous
Next

Latest White Papers

  • Shifting Plans for 2020 & Beyond
  • Game On: How Brands Can Log Into A Diverse Multi-Billion Dollar Industry
  • What CCPA Means For Brands
  • How Google is Improving Consumer Data Privacy
  • Ways to Prepare for the Cookieless Future
  • See all White Papers

Featured Posts

  • Ad Age Names PMG #1 Best Place to Work in 2021
  • Hindsight 2020 & Looking Ahead to 2021
  • Preparing for Streaming’s Growth & The Future of TV Buying
  • MediaPost Names PMG Independent Agency of the Year
  • PMG Client Portfolio Trends During Amazon Prime Day 2020

Categories

  • Consumer Insights
  • Content
  • Creative Design
  • Data Analytics
  • Development
  • Digital TV & Video
  • Ecommerce
  • Industry News
  • Local
  • Mobile
  • Paid Search
  • PMG Culture
  • Programmatic & Display
  • SEO
  • Social Media
  • Structured Data
Fort Worth

2845 West 7th Street
Fort Worth, TX 76107

Dallas

3102 Oak Lawn Avenue
Suite 650
Dallas, TX 75219

Austin

823 Congress Avenue
Suite 800
Austin, TX 78701

London

33 Broadwick Street
London
W1F 0DQ

New York

120 East 23rd Street
New York, NY 10010

Get in touch

(817) 420 9970
info@pmg.com

Subscribe to the PMG Newsletter
© 2021 PMG Worldwide, LLC, All Rights Reserved
  • Contact
  • Privacy Policy
 Tweet
 Share
 Tweet
 Share
 Tweet
 Share
 LinkedIn
We and our partners use cookies to personalize content, analyze traffic, and deliver ads. By using our website, you agree to the use of cookies as described in our Cookie Policy.