SQL query LIKE statement error

Question

I am new to mysql database. I have to make a query to filter name_cache which includes TNY or eth0 in my query! This is my current query--

SELECT data_template_data.data_source_path, data_template_data.name_cache
FROM data_local, data_template_data,host
WHERE data_local.id=data_template_data.local_data_id AND
      data_local.host_id=host.id
HAVING data_template_data.name_cache like "%TNY%eth0"
ORDER BY data_local.id;

---

    data_source_path                               | name_cache                      
| <path_rra>/r1f-sinx03ztny_traffic_in_15846.rrd | R1F-SINX03ZTNY - Traffic - eth0 |
| <path_rra>/r1f-sinx04ztny_traffic_in_15857.rrd | R1F-SINX04ZTNY - Traffic - eth0 |
| <path_rra>/r1f-wzrx79ztny_traffic_in_16343.rrd | R1F-WZRX79ZTNY - Traffic - eth0 |

I want to only validate with tsp short name(TNY in WZRX79ZTNY). Help me how should i query for it?

Answer 1

First, never use commas in the FROM clause. Always use proper explicit JOIN syntax. So, your query (with table aliases) is:

SELECT td.data_source_path, td.name_cache
FROM data_local l JOIN
     data_template_data td 
     ON l.id = td.local_data_id
     host h
     ON l.host_id = h.id
WHERE td.name_cache like '%TNY%eth0'
ORDER BY l.id;

You are not using host (unless to check that l.host_id is not null). Similarly, you don't need data_local. So, I think this does the same thing that your query does:

SELECT td.data_source_path, td.name_cache
FROM data_template_data td
WHERE td.name_cache like '%TNY%eth0'
ORDER BY td.local_data_id;

For this query, there is not much you can do. You might be able to use an index on data_template_data(local_data_id). Unfortunately, the wildcard at the beginning of the like prevents the use of an index.

If you are looking for words, you might be able to use a full text index.

Answer 2

use WHERE, not HAVING. also using single quote, not double quote consider using JOIN so the table relation ship more clear

SELECT data_template_data.data_source_path, data_template_data.name_cache 
FROM data_local
INNER JOIN data_template_data ON data_local.id=data_template_data.local_data_id
INNER JOIN host ON data_local.host_id=host.id
WHERE data_template_data.name_cache like '%TNY%eth0' 
ORDER BY data_local.id;

but i don't see you use anything from table host so i think you can exclude that from your query

SELECT data_template_data.data_source_path, data_template_data.name_cache 
    FROM data_local
    INNER JOIN data_template_data ON data_local.id=data_template_data.local_data_id
    WHERE data_template_data.name_cache like '%TNY%eth0' 
    ORDER BY data_local.id;