0

I have a table like this:

ID  URLIdentifier

1   google.com

2   youtube.com

3   baidu.com

and another table like this

URLID URL

1   www.google.com/webhp?sourceid=chrome-instant&ion=1&espv=2&ie=UTF-8#q=123

2   www.youtube.com/channel/UCl8dMTqDrJQ0c8y23UBu4kQ

3   www.google.com/webhp?sourceid=chrome-instant&ion=1&espv=2&ie=UTF-8#q=fadsfsafsa

4   www.baidu.com/s?wd=fdsafa&rsv_spt=1&issp=1&f=8&rsv_bp=0&rsv_idx=2&ie=utf-8&tn=baiduhome_pg&rsv_enter=0&rsv_sug3=7&inputT=1009

5   www.youtube.com/watch?v=5qanlirrRWs&list=PLbpi6ZahtOH5r5PlY8nQjm41h3UL_x8gl

6   www.youtube.com/watch?v=5aeKdmw9W50

7   v.baidu.com/v?ct=301989888&rn=20&pn=0&db=0&s=25&word=fdsafa&ie=utf-8&ie=utf-8

I want to select all the rows which the URL contain the URLIdentifier and here is the result like:

ID  URLIdentifier   URLID   URL

1   google.com  1   www.google.com/webhp?sourceid=chrome-instant&ion=1&espv=2&ie=UTF-8#q=123'

1   google.com  3   www.google.com/webhp?sourceid=chrome-instant&ion=1&espv=2&ie=UTF-8#q=fadsfsafsa'

2   youtube.com 2   www.youtube.com/channel/UCl8dMTqDrJQ0c8y23UBu4kQ

2   youtube.com 5   www.youtube.com/watch?
v=5qanlirrRWs&list=PLbpi6ZahtOH5r5PlY8nQjm41h3UL_x8gl

2   youtube.com 6   www.youtube.com/watch?v=5aeKdmw9W50

3   baidu.com   4   www.baidu.com/s?wd=fdsafa&rsv_spt=1&issp=1&f=8&rsv_bp=0&rsv_idx=2&ie=utf-8&tn=baiduhome_pg&rsv_enter=0&rsv_sug3=7&inputT=1009

3   baidu.com   7   v.baidu.com/v?ct=301989888&rn=20&pn=0&db=0&s=25&word=fdsafa&ie=utf-8&ie=utf-8

Imagine there are hundreds of URLIdentifier and millions of URLs so I will need a script that select all rows.

halfer
  • 19,824
  • 17
  • 99
  • 186
Jack
  • 281
  • 1
  • 3
  • 17

1 Answers1

2

You need to use a join to combine the data together. Then there are a couple different ways to get the matching records. Here's one using like:

select t1.ID, t1.URLIdentifier, t2.URLID, t2.URL
from table1 t1
    inner join table2 t2 on t2.URL like '%' + t1.URLIdentifier + '%'
sgeddes
  • 62,311
  • 6
  • 61
  • 83
  • thx man that helps a lot~! but there is one more thing is that in the URLIdentifier, if there is more than 1 website and they are separated by ',' lets say 3 different website as URLIdentifier, what should I do to do that using the code that you right before? – Jack Dec 05 '14 at 15:03
  • @Jack -- I wouldn't recommend storing data in that nature. It's very difficult to traverse. You're going to need to look into a splitting function to split the results, and then use something like the posted solution. This might help you get going: http://stackoverflow.com/questions/1096679/can-mysql-split-a-column – sgeddes Dec 05 '14 at 15:12
  • Thx Sgeddes, but the code didnt give me any results i dont know why, it looks good to me. – Jack Dec 05 '14 at 17:03
  • hi i tried the code and it doesnt work cuz '%' + t1.URLIdentifier + '%' this part doesnt work. i used concat('%', t1.URLIdentifier, '%') and it works! – Jack Dec 05 '14 at 19:17