Combine Multiple MySQL Queries on the Same Table I

Question

How do you make multiple queries on the same table, selecting different columns?

If it helps at all... All of the queries have a common column in the select part of the SQL statement. They all select the ID, then followed by something specific.

So every query needs the ID and either of the following: post_name, post_title, or post_excerpt.

Also if it helps to simplify things. I'm trying to search for broad matches and exact matches in these columns.

So in my example, I will be looking for: "floor finish", "floor", or "finish" in the following columns: post_name, post_title, and post_excerpt. All within the same table.

I've attempted to accomplish this with UNION.

Here are my queries:

Array
(
    [broad] => Array
        (
            [floor] => Array
                (
                    [slugs] => SELECT `ID`, `post_name` FROM tps_3_posts WHERE `post_status` = 'publish' AND `post_name` LIKE '%floor%'
                    [titles] => SELECT `ID`, `post_title` FROM tps_3_posts WHERE `post_status` = 'publish' AND `post_title` LIKE '%floor%'
                    [excerpts] => SELECT `ID`, `post_excerpt` FROM tps_3_posts WHERE `post_status` = 'publish' AND `post_excerpt` LIKE '%floor%'
                )

            [finish] => Array
                (
                    [slugs] => SELECT `ID`, `post_name` FROM tps_3_posts WHERE `post_status` = 'publish' AND `post_name` LIKE '%finish%'
                    [titles] => SELECT `ID`, `post_title` FROM tps_3_posts WHERE `post_status` = 'publish' AND `post_title` LIKE '%finish%'
                    [excerpts] => SELECT `ID`, `post_excerpt` FROM tps_3_posts WHERE `post_status` = 'publish' AND `post_excerpt` LIKE '%finish%'
                )

        )

    [exact] => Array
        (
            [slugs] => SELECT `ID`, `post_name` FROM tps_3_posts WHERE `post_status` = 'publish' AND `post_name` LIKE '%floor-finish%'
            [titles] => SELECT `ID`, `post_title` FROM tps_3_posts WHERE `post_status` = 'publish' AND `post_title` LIKE '%floor finish%'
            [excerpts] => SELECT `ID`, `post_excerpt` FROM tps_3_posts WHERE `post_status` = 'publish' AND `post_excerpt` LIKE '%floor finish%'
        )

    [combined] => ( SELECT `ID`, `post_name` FROM tps_3_posts WHERE `post_status` = 'publish' AND `post_name` LIKE '%floor-finish%' ) UNION ( SELECT `ID`, `post_name` FROM tps_3_posts WHERE `post_status` = 'publish' AND `post_name` LIKE '%floor%' ) UNION ( SELECT `ID`, `post_name` FROM tps_3_posts WHERE `post_status` = 'publish' AND `post_name` LIKE '%finish%' ) UNION ( SELECT `ID`, `post_title` FROM tps_3_posts WHERE `post_status` = 'publish' AND `post_title` LIKE '%floor finish%' ) UNION ( SELECT `ID`, `post_title` FROM tps_3_posts WHERE `post_status` = 'publish' AND `post_title` LIKE '%floor%' ) UNION ( SELECT `ID`, `post_title` FROM tps_3_posts WHERE `post_status` = 'publish' AND `post_title` LIKE '%finish%' ) UNION ( SELECT `ID`, `post_excerpt` FROM tps_3_posts WHERE `post_status` = 'publish' AND `post_excerpt` LIKE '%floor finish%' ) UNION ( SELECT `ID`, `post_excerpt` FROM tps_3_posts WHERE `post_status` = 'publish' AND `post_excerpt` LIKE '%floor%' ) UNION ( SELECT `ID`, `post_excerpt` FROM tps_3_posts WHERE `post_status` = 'publish' AND `post_excerpt` LIKE '%finish%' )
)

However, the above result is interesting. I appear to get all the correct results except the key of each result value (which is supposed to be the name of the column) always remains the same. It's always post_name even though the value assigned to it might be a post_title or post_excerpt.

So every result has an ID and post_name. Basically they keys are wrong but the values appear to be accurate.

I also tried something like this:

Array
        (
            [broad] => Array
                (
                    [floor] => SELECT `ID`, `post_name`, `post_title`, `post_excerpt` FROM tps_3_posts WHERE `post_status` = 'publish' AND ( `post_name` LIKE '%floor%' OR `post_title` LIKE '%floor%' OR `post_excerpt` LIKE '%floor' )
                    [finish] => SELECT `ID`, `post_name`, `post_title`, `post_excerpt` FROM tps_3_posts WHERE `post_status` = 'publish' AND ( `post_name` LIKE '%finish%' OR `post_title` LIKE '%finish%' OR `post_excerpt` LIKE '%finish%' )
                )

            [exact] => SELECT `ID`, `post_name`, `post_title`, `post_excerpt` FROM tps_3_posts WHERE `post_status` = 'publish' AND ( `post_name` LIKE '%floor-finish%' OR `post_title` LIKE '%floor finish%' OR `post_excerpt` LIKE '%floor finish%' )
            [combined] => SELECT `ID`, `post_name`, `post_title`, `post_excerpt` FROM tps_3_posts WHERE `post_status` = 'publish' AND ( `post_name` LIKE '%floor-finish%' OR `post_title` LIKE '%floor finish%' OR `post_excerpt` LIKE '%floor finish%' ) UNION (SELECT `ID`, `post_name`, `post_title`, `post_excerpt` FROM tps_3_posts WHERE `post_status` = 'publish' AND ( `post_name` LIKE '%floor%' OR `post_title` LIKE '%floor%' OR `post_excerpt` LIKE '%floor%' )) UNION (SELECT `ID`, `post_name`, `post_title`, `post_excerpt` FROM tps_3_posts WHERE `post_status` = 'publish' AND ( `post_name` LIKE '%finish%' OR `post_title` LIKE '%finish%' OR `post_excerpt` LIKE '%finish%' ))
        )

)

This is more along the lines of what I'm trying to accomplish. I would like each result to have ID, post_excerpt, post_slug, and post_title. If there's no match, display they key with an empty value, or just completely don't even display the key.

The problem with the second attempt is that it's only requiring a match in one of the three desired columns. So if it matched in the post_excerpt and no where else, it will still pull values from the post_title and post_name. Thus making the results inaccurate.

I've read several what appear to be similar questions however most don't have real solid clear answers... OR ... The questions/answers are more geared to multiple queries on SEPARATE tables.

Any guidance or advice on combining multiple MySQL queries on the same table?

BY THE WAY... I am using the "combined" in both my examples as my final query to send to the database.

So just one more time... If there's no match in a column, display they key as null or just completely omit the key from the results entirely.

Answer 1

As I understood in second case the problem is that only matched column should have value and others should be null. For this case you can use IF/ELSE or WHEN statement in SELECT. The idea is you select actual value is value of this column corresponds to expected value and null otherwise.

Another potential solution is you can write e.g.

SELECT `ID`, `post_name`, `post_title`, 'post_excerpt'

So you select string value that were criteria in WHERE statement and can use this value later to understand what this row belongs to.

Answer 2

You're getting the wrong "key" because of the UNION statement in your query. You have different key names, but compatible column types between the three different columns your unioned queries. Rather than throw an error, the database engine is just picking the column name from the first query and using that for all of them:

id | post_name # <= column name in first query
1  | "my post"

UNION

id | post_title # <= column name is different, but type is compatible, so UNION succeeds
1  | "my post title"

UNION

id | post_excerpt # <= ditto
1  | "my post excerpt"

would result in:

id | post_name # <= column name from first query
1  | "my post"
1  | "my post title"
1  | "my post excerpt"

which is what you are experiencing.

Instead, you could do something like:

id | post_name | post_title | post_excerpt
1  | "my post" | null       | null        # <= deliberately select nulls for these columns in this query

UNION

id | post_name | post_title      | post_excerpt
1  | null      | "my post title" | null

UNION

id | post_name | post_title | post_excerpt
1  | null      | null       | "my post excerpt"

Which would give you results like:

id | post_name | post_title      | post_excerpt
1  | "my post" | null            | null
1  | null      | "my post title" | null
1  | null      | null            | "my post excerpt"

With your table, a very basic version of this might look like:

SELECT ID, post_name, null AS post_title, null AS post_excerpt FROM tps_3_posts

UNION

SELECT ID, null AS post_name, post_title, null AS post_excerpt FROM tps_3_posts

UNION

SELECT ID, null AS post_name, null AS post_title, post_excerpt FROM tps_3_posts

which might be more usable for what you are trying to do. Here's a SQLFiddle if you want to see it in action.