Как сделать выборку из нескольких таблиц с помощью оператора LIKE?

Question

[dinozawrbeatch]

Есть код:

SELECT  materials.id, 
        materials.title, 
        materials.author, 
        materials.description, 
        types.name AS type, 
        categories.name AS category 
        FROM materials 
        INNER JOIN tags_to_materials 
        ON materials.id = tags_to_materials.material_id
        INNER JOIN categories 
        ON materials.id_category = categories.id 
        INNER JOIN types 
        ON materials.id_type = types.id 
        INNER 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%'

Нужно найти по входящим строкам все совпадения из таблиц (по title, category, tag, author).
Пожалуйста помогите

Answer 1

[Дмитрий]

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);