SQL combine records with same wildcard value

Question

I have a MySQL Wordpress database and I'm dealing with the postmeta table which isn't nicely structure. It has four columns: "meta_id, post_id, meta_key, meta_value". This is the query I am working with:

SELECT  DISTINCT * 
        FROM wp_10_postmeta as pm
        JOIN wp_10_posts AS p ON pm.post_id = p.ID
        WHERE (meta_key LIKE 'personnel_%_last_name' OR
                meta_key LIKE 'personnel_%_first_name' OR
                meta_key LIKE 'personnel_%_title' OR
                meta_key LIKE 'personnel_%_alias' OR
                meta_key LIKE 'webpages_%_title' OR
                meta_key LIKE 'webpages_%_alias' OR
                meta_key LIKE 'departments_%_name' OR
                meta_key LIKE 'departments_%_alias')
            AND post_status = 'publish' AND meta_value LIKE '%john%' AND meta_value LIKE '%smith%'

Issue: This query works great if I search for "john" or "Smith" but if I do "John Smith" it return empty. Is there a way to combine "personnel_%_last_name" and "personnel_%_first_name" and then compare?

It's a directory of employees and departments, so pretty much just strings. — thegreyspot

Gordon Linoff Gordon Linoff · Accepted Answer · 2014-09-17T23:49:29

I am guessing that you want to look across multiple fields for the match. Try using a group by for this purpose:

    SELECT p.*
    FROM wp_10_postmeta as pm JOIN
         wp_10_posts AS p
         ON pm.post_id = p.ID
    WHERE (meta_key LIKE 'personnel_%_last_name' OR
            meta_key LIKE 'personnel_%_first_name' OR
            meta_key LIKE 'personnel_%_title' OR
            meta_key LIKE 'personnel_%_alias' OR
            meta_key LIKE 'webpages_%_title' OR
            meta_key LIKE 'webpages_%_alias' OR
            meta_key LIKE 'departments_%_name' OR
            meta_key LIKE 'departments_%_alias')
        AND p.post_status = 'publish'
    GROUP BY p.id
    HAVING SUM(meta_value LIKE '%john%') > 0 AND SUM(meta_value LIKE '%smith%') > 0;

SQL combine records with same wildcard value

1 Answers