first of all i have 1 table in 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 bunch of id of tags in 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' need to take from query1)
I am getting output proper but need to use 2 query.I want to do this in single query.
Thanks
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 a better option
If you whant to get the 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;
maybe you can use query :
select id from tags where name = (select name from tags where id = 1 ).
You can try that query.
For performance better 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 isn't valid in case that you have theme1=test1 and theme1=test10 values.