dinozawrbeatch

Left join - у нас все таки OR.

SELECT materials.id,
       materials.title,
       materials.author,
       materials.description,
       types.name      AS type,
       categories.name AS category
FROM materials
         LEFT JOIN categories
                   ON materials.id_category = categories.id
         LEFT JOIN types
                   ON materials.id_type = types.id
         LEFT JOIN tags_to_materials
                   ON materials.id = tags_to_materials.material_id
         LEFT JOIN tags
                   ON tags.id = tags_to_materials.tag_id
WHERE materials.author LIKE '$str%'
   OR materials.title LIKE '$str%'
   OR categories.name LIKE '$str%'
   OR tags.name LIKE '$str%'

Однако некоторые записи будут возвращаться дважды 0 ибо tags - materials многие ко многим. Если это не устраивает то задача называется groupwise maximum и одно из решений:

select materials.id,
       materials.title,
       materials.author,
       materials.description,
       types.name      AS type,
       categories.name AS category
FROM materials
         LEFT JOIN categories
                   ON materials.id_category = categories.id
         LEFT JOIN types
                   ON materials.id_type = types.id
         LEFT JOIN tags_to_materials
                   ON materials.id = tags_to_materials.material_id
         LEFT JOIN tags
                   ON tags.id = tags_to_materials.tag_id
where materials.id in (
    SELECT materials.id
    FROM materials
             LEFT JOIN categories
                       ON materials.id_category = categories.id
             LEFT JOIN types
                       ON materials.id_type = types.id
             LEFT JOIN tags_to_materials
                       ON materials.id = tags_to_materials.material_id
             LEFT JOIN tags
                       ON tags.id = tags_to_materials.tag_id
    WHERE materials.author LIKE '$str%'
       OR materials.title LIKE '$str%'
       OR categories.name LIKE '$str%'
       OR tags.name LIKE '$str%' group by materials.id);