Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Check if the value of one column contains the value of another

Tags:

sql

mysql

I have a table with java package names:

|id|pkg|
|1 |some.long.package.path|
|2 |com.admob.bla|

and a table that contains partial packages names form known advertisement frameworks.

|id|path_fragment|
|1 |.admob |

How can I retrieve packages whose value for pkg contains any of the ad_frameworks.path_fragment?

I just can't find the substring check. All I find is people using like to compare to some string literal but never for comparing columns.

NOTE: I am using MySQL.

like image 681
er4z0r Avatar asked Sep 14 '12 12:09

er4z0r


People also ask

How do you lookup the value of a value in one column and return another?

Combining INDEX and MATCH to Lookup Value in Column and Return Value of Another Column. Apart from that, you can use nested INDEX and MATCH formulas to lookup for a value in a column and get the result of another column in the dataset.


2 Answers

think you could do an inner join with a like

select p.id, p.pkg
from package p
inner join ad_frameworks adf on p.pkg like CONCAT('%', adf.path_fragment, '%')
--group by p.id, p.pkg

or as you pointed

select * 
from package p
inner join ad_frameworks adf on LOCATE(adf.fragment, p.pkg) > 0

or

select * 
from package p
inner join ad_frameworks adf on INSTR( p.pkg,adf.fragment) > 0

or

select * 
from package p
inner join ad_frameworks adf on POSITION(adf.fragment IN p.pkg) > 0

or

select * 
from package p
inner join ad_frameworks adf on REPLACE(p.pkg, adf.fragemnt, '') <> p.pkg

Well, you've got few choices ;)

SqlFiddle

like image 119
Raphaël Althaus Avatar answered Nov 15 '22 06:11

Raphaël Althaus


Try this,

SELECT  a.*, b.*
FROM    package a, ad_frameworks b
WHERE   a.pkg LIKE CONCAT('%',b.path_fragment,'%')

SQLFiddle Demo

like image 38
John Woo Avatar answered Nov 15 '22 05:11

John Woo