Splitting rows and subquery in mysql

Question

First of all, I have 1 table in the database.

1) tags:

id    name
1     theme1=test1
2     theme1=test2
3     theme1=test3
4     theme2=test1
5     theme2=test2
6     theme2=test3

And I have a set id

of tags

in an array. like 1.3.

Now

1) select name from tags where id = 1

result: theme1 = test1

(now using wildcard)

2) select id from tags where name like 'theme_test1'

result: 1.4

(here 'theme_test1' should be taken from query1)

I am getting the output correctly, but I need to use 2 queries. I want to do this in one request.

thank

+3

hemsbhardiya June 16 15 at 10:43

4 answers

If you want to get an ID with the same value, you can try this:

SELECT t2.* FROM yourtable t1
JOIN youtable t2 on ON  t2.name like concat(substr(t1.name,1,5), '%', substr(t1.name,8))
WHERE t1.id=1;

0

codeneuss June 16 15 at 11:06

For efficiency, use this:

SELECT t.id 
FROM r 
INNER JOIN r AS t ON t.name LIKE CONCAT('theme_=test', SUBSTRING(r.name,-1))
WHERE r.id = '1'

r

is your table in this case.

NOTE : this answer is not valid in case you have themes1 = test1 and theme1 = test10.

0

Reynier de la Rosa June 16 15 at 11:54

maybe you can use query:

select id from tags where name = (select name from tags where id = 1).

You can try this query.

-1

Soepeno ng June 16 15 at 11:07

Steve · Accepted Answer · 2015-06-16T11:08:10+0000

SELECT id FROM tags WHERE name LIKE ( 
    SELECT CONCAT(SUBSTRING(name,1,5),'__',SUBSTRING(name,8)) FROM tags WHERE id=1 
)

Returns 1.4

But two queries (or a refactor) might be the best option