CODEWITHSUNDEEP
mysqlsql
Takeshi Tawarada
Takeshi Tawarada

Reputation: 113

Join Two Tables and Replace Values Where Conditions Meet

I'm trying to come with an expression to join two tables (the first two) to get something like the third one.

I want to SELECT 'Sitepage' and 'Medium' and JOIN the first two tables ON the rows where the RIGHT 5 characters are matching between 'Sitepage' and 'Campaign ID'. Additionally, IF there is a match, THEN 'Program' will REPLACE 'Medium'. What would be the Syntax?

Sitepage | Medium                 
xyz.com/campaign=12345 | A           
xyz.com/campaign=23456 | C        

Campaign ID | Program                 
12345 | B           

Sitepage | Medium                 
xyz.com/campaign=12345 | B           
xyz.com/campaign=23456 | C

https://i.sstatic.net/pq35n.png

Upvotes: 1

Views: 79

Answers (3)

Phil Walton
Phil Walton

Reputation: 963

I based my answer off of @Juan's, but I had to make some adjustments to get it to work.

SELECT
  SitePage, COALESCE(t2.Program, t1.Medium) as Medium
FROM Table1 t1
LEFT JOIN Table2 t2
ON RIGHT(t1.Sitepage, 5) = COALESCE(t2.`Campaign ID`, -1);

@Abhik was heading in the right direction too. It's more generic than the one above, which assumes that the last 5 characters of SitePage will be the only pertinent ones. With that said, I would have gone with...

SELECT
  SitePage, COALESCE(t2.Program, t1.Medium) as Medium
FROM Table1 t1
LEFT JOIN Table2 t2
  ON SUBSTRING_INDEX(t1.Sitepage,'=',-1)
    = COALESCE(t2.`Campaign ID`, -1);

SQL Fiddle example

Upvotes: 2

Juan
Juan

Reputation: 3705

SELECT SitePage, ISNULL(t2.Program, t1.Medium) as Medium
FROM Table1 t1
LEFT JOIN Table2 t2 ON RIGHT(t1.Medium, 5) = t2.CampaignId

That should do the trick. Left Join on the expresion you want, when there is a match, Program will not be null so you can pick it up, otherwise pick medium. If your table2 allows null on Program you may need to tweak this.

Upvotes: 1

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

You can use substring_index and then update by join

update table1 t1
join table2 t2 on t2.`Campaign ID` = substring_index(t1.`Sitepage`,'=',-1)
set t1.`Medium` = t2.`Program`

Upvotes: 1

Related Questions