selecting LIKE value IN subquery

Question

Possible Duplicate:
MySQL LIKE IN()?

I have 2 tables

atm_name_initials (initial)

KS
FW
GE
SD

atm_tasks_dit (task_name,task_given_to)

task1 KS
task2 GE
task3 GE/SD

Now i want to SELECT all distinct names from atm_name_initials WHERE they are founded IN LIKE atm_tasks_dit.task_given_to

This is what i have so far:

SELECT initial FROM atm_name_initials
WHERE initial 
IN
(
    SELECT DISTINCT task_given_to FROM atm_tasks_dit
    WHERE on_big_project_id = 29
)

What this piece of code does it searches if EXACLY the atm_name_initials.initial value is founded in atm_tasks_dit.task_given_to

i would like something like

SELECT initial FROM atm_name_initials
WHERE initial 
"IS LIKE" IN

'%
(
    SELECT DISTINCT task_given_to FROM atm_tasks_dit
    WHERE on_big_project_id = 29
)
%'

or something...

Any help would be appreciated, thank you!

in atm_name_initials there are no duplicates, but in atm_tasks_dit might be — Empeus, Aug 23 '12 at 12:29

score 2 · Accepted Answer · edited May 23 '17 at 10:34

2

A mix between answers proposed, using MySQL sintax:

SELECT DISTINCT initial
  FROM atm_tasks_dit,atm_name_initials
 WHERE task_given_to LIKE CONCAT('%',initial,'%')

Just in case check SQL Fiddle code.

And here is another similar question.

edited May 23 '17 at 10:34

Community

1
1

answered Aug 23 '12 at 13:05

Yaroslav

6,476
10
48
89

hey, thank you it works! but in that example i see a INNER JOIN, and what type of select is this? SELECT FROM table1,table2 ... is like a union,join?? what is it? – Empeus Aug 23 '12 at 13:20
You are welcome. This is equivalent to a `INNER JOIN`. Check this [question/answer](http://stackoverflow.com/questions/10694527/sql-join-notation-is-this-equivalent-to-inner-or-full-join) for more details. – Yaroslav Aug 23 '12 at 13:32
ok, this is a inner join, ok? so this translates as http://www.sqlfiddle.com/#!2/9eec3/22 ? because it's more readable and logical – Empeus Aug 23 '12 at 13:56
Yes, you got it right. I usually use the `JOIN` but this time I used this other way of doing in it ;) – Yaroslav Aug 23 '12 at 16:34

Joe G Joseph · Answer 2 · 2012-08-23T12:10:00.183

0

try this:

SELECT initial 
FROM   atm_name_initials
join   atm_tasks_dit
on     initial like concat('%',task_given_to,'%')
WHERE  on_big_project_id = 29

edited Aug 23 '12 at 12:10

answered Aug 23 '12 at 12:03

Joe G Joseph

23,518
5
56
58

ok, but this is MySQL, i have to find a method to concat this? – Empeus Aug 23 '12 at 12:06
the idea is that i have to find everyone that had worked in that project, not just flush all the name initials – Empeus Aug 23 '12 at 12:09

score 0 · Answer 3 · answered Aug 23 '12 at 12:13

0

Try

Select distinct ani.initial--atd.task_name,atd.task_given_to
From atm_tasks_dit atd
join atm_name_initials ani on atd.task_given_to like '%' + ani.initial + '%'
-- WHERE on_big_project_id = 29

Result

initial

GE 
KS 
SD

I did in Sql Server as I don't have MySql install

answered Aug 23 '12 at 12:13

Niladri Biswas

4,153
2
17
24

thank you for your answer, but i'm using mysql – Empeus Aug 23 '12 at 13:53

selecting LIKE value IN subquery

3 Answers3