0

Okay I have table: ad-domains with one column and I want to find all rows in another table called history with 3 columns: key, domain, time.

I want to do something akin to 

SELECT * FROM history WHERE domain LIKE "%{ALL_DOMAIN_COLUMN_FROM_ad-domains_TABLE}"

Is this possible?

Some Data for an example:

ad-domain:

domain
------
ads.com
moreads.com
gottahaveads.com

history:

key      domain
-----------------------------------
1        google.com
2        subads.ads.com
3        gmail.com
4        moreads.com
5        whoa.moreads.com
6        000.hi.whoa.moreads.com
7        ads.com.taco

The result should be

key      domain
------------------------------------
2        subads.ads.com
4        moreads.com
5        whoa.moreads.com
6        000.hi.whoa.moreads.com
Jeff
  • 480
  • 1
  • 5
  • 17
  • You will need to `JOIN` the two tables on `LIKE` condition. If there could be more than one match, then you will need to `DISTINCT` the result. Or, if you are not allowed to use `JOIN` you can have `WHERE EXISTS`. – PM 77-1 Aug 10 '18 at 17:42

1 Answers1

1

The query you're looking for is something like:

SELECT * FROM history
INNER JOIN ad-domain ON history.domain LIKE CONCAT('%', ad-domain.domain, '%')

Here is more information on the topic.

Ildar Akhmetov
  • 1,331
  • 13
  • 22
  • Hmm never occurred to me to use a LIKE on a join condition. This does it(pretty much, just had to drop the first '%' in the concat statement). Although this is going to take an insane amount of time to run on a 2GB table I'd imagine. – Jeff Aug 10 '18 at 17:52