PHP在db中的两个表中搜索

Question

I would like to search files by name and after the tag.

If the tag assigned to the file matches the word you are looking for, this file is also counted.

I have three tables :

tags
id | name

tags_files
id | id_tag | id_file

files
id | name | category

Now i only search by name in "files" table.

$how_many = $this->wpdb->get_var($this->wpdb->prepare("SELECT COUNT(*) FROM {$this->table_files} WHERE name LIKE %s AND category IN ($subcategories)", '%' .  $this->wpdb->esc_like($search) . '%')); 

Every file has assigned tags in "tags_files" table.

How solve this?

=========================

If want you can add search in category like this:

WHERE (tags.name LIKE '%search%' OR files.name LIK '%search%' ) AND ($this->files.category IN ($subcategories))

Answer 1

SELECT files.* FROM files
LEFT JOIN tags_files ON tags_files.id_file = files.id
LEFT JOIN tags ON tags.id = tags_files.id
WHERE tags.name LIKE '%search%' OR files.name LIK '%search%'