2

I am using this query to get results from two tables:

SELECT * FROM (
    SELECT parent_id as mID, count(*) as cnt
        FROM wp_forum_posts
        WHERE text LIKE '%{$word}%'
        GROUP by 1
        UNION ALL
            SELECT id, count(*)
            FROM wp_forum_threads
            WHERE subject LIKE '%{$word}%'
            GROUP by 1) x
ORDER BY 2, 1

I want to select some more values from wp_forum_threads. Values like subject. How can I do this? Simply adding behind id does not work. the query returns no result, then.

hakre
  • 193,403
  • 52
  • 435
  • 836
user998163
  • 471
  • 1
  • 9
  • 28
  • 3
    Your columns have to match up, but you can substitute an empty string for the missing column in that SELECT. You still alias it with AS. – DOK Jan 04 '12 at 00:03
  • 2
    In other words, the number of columns in each SELECT being UNIONed has to be the same. – Robert Harvey Jan 04 '12 at 00:04

1 Answers1

3

The number of columns in the the select on both parts of the UNION ALL should be the same. That means that if for example you want to add "subject" to the 2nd part of the query, you'll need to add a "place holder" in the 1st part of the query is well:

    SELECT * FROM (
SELECT parent_id as mID, NULL, count(*) as cnt
    FROM wp_forum_posts
    WHERE text LIKE '%{$word}%'
    GROUP by 1
    UNION ALL
        SELECT id, subject, count(*)
        FROM wp_forum_threads
        WHERE subject LIKE '%{$word}%'
        GROUP by 1) x
ORDER BY 2, 1

this should work.

mr.tenuki
  • 81
  • 2