combine two select statements (append columns)

Question

I have 2 Wordpress database queries, the first shows all posts + categories. The second shows all posts + custom fields.

What I'm looking for is listing all posts + categories + custom fields

Query 1: list posts + categories

SELECT ID,
post_title,

(SELECT group_concat(wp_terms.name separator ", ") FROM wp_terms
INNER JOIN wp_term_taxonomy on wp_terms.term_id = 
wp_term_taxonomy.term_id
INNER JOIN wp_term_relationships wpr on wpr.term_taxonomy_id = 
wp_term_taxonomy.term_taxonomy_id
WHERE taxonomy="category" and wp_posts.ID = wpr.object_id ) AS 
"Categories"
FROM wp_posts WHERE post_type = "post" AND post_status = "publish"

+------+--------------------------------------+------------+
| ID   | post_title                           | Categories |
+------+--------------------------------------+------------+

Query 2: list posts + custom fields

SELECT ID, post_title, pm1.meta_value as "Amazon.com", pm2.meta_value 
as "Amazon.co.uk" FROM wp_posts
LEFT JOIN wp_postmeta pm1 ON ID = pm1.post_id AND 
pm1.meta_key = "Amazon.com"
LEFT JOIN wp_postmeta pm2 ON ID = pm2.post_id AND 
pm2.meta_key = "Amazon.co.uk"
WHERE post_type = "post" AND post_status = "publish"

+------+-----------------------+--------------+--------------+
| ID   | post_title            | Amazon.com   | Amazon.co.uk |
+------+-----------------------+--------------+--------------+

How can I combine the results so that I can list all posts + categories + custom fields?

+------+-------------+-------------+--------------+--------------+
| ID   | post_title  | Categories  | Amazon.com   | Amazon.co.uk |
+------+-------------+-------------+--------------+--------------+

You can create two different views and combine it later using joins, Can it be a solution ? — jgoday, Mar 16 '19 at 21:41
Perhaps you are looking for `UNION ALL` and have default placeholders for the columns, so they all appear in one query — Miroslav Glamuzina, Mar 16 '19 at 21:43

score 3 · Accepted Answer · answered Mar 16 '19 at 21:50

Try using the following query:

SELECT ID, post_title, pm1.meta_value as "Amazon.com", pm2.meta_value as "Amazon.co.uk" , 
                                        (SELECT group_concat(wp_terms.name separator ", ") FROM wp_terms
                                        INNER JOIN wp_term_taxonomy on wp_terms.term_id = 
                                        wp_term_taxonomy.term_id
                                        INNER JOIN wp_term_relationships wpr on wpr.term_taxonomy_id = 
                                        wp_term_taxonomy.term_taxonomy_id
                                        WHERE taxonomy="category" and wp_posts.ID = wpr.object_id ) AS "Categories"
FROM wp_posts
LEFT JOIN wp_postmeta pm1 ON ID = pm1.post_id AND 
pm1.meta_key = "Amazon.com"
LEFT JOIN wp_postmeta pm2 ON ID = pm2.post_id AND 
pm2.meta_key = "Amazon.co.uk"
WHERE post_type = "post" AND post_status = "publish"

He's got a `LEFT JOIN` on there so it's likely it's a one to many relationship. Did you mean to throw a `GROUP_CONCAT()` on the `meta_value` columns? — Miroslav Glamuzina, Mar 16 '19 at 21:56
@MiroslavGlamuzina The subquery is to get all categories related to the post, it is not related to the left joins. — Hadi, Mar 16 '19 at 22:02

Miroslav Glamuzina · Answer 2 · 2019-03-16T21:54:08.773

In order to get all the columns in the same result is to 'merge' the two queries together and SELECT the unique columns listed in each of the queries.

Something like this should do the trick:

SELECT 
  ID,
  post_title,
  (SELECT 
     GROUP_CONCAT(wp_terms.name SEPARATOR ", ") 
   FROM 
     wp_terms
     INNER JOIN wp_term_taxonomy on wp_terms.term_id=wp_term_taxonomy.term_id
     INNER JOIN wp_term_relationships wpr on wpr.term_taxonomy_id= wp_term_taxonomy.term_taxonomy_id
   WHERE 
     taxonomy="category"   
     AND wp_posts.ID=wpr.object_id 
  ) AS `Categories`,
  GROUP_CONCAT(pm1.meta_value) AS `Amazon.com`, 
  GROUP_CONCAT(pm2.meta_value) AS `Amazon.co.uk`
FROM 
  wp_posts 
  LEFT JOIN wp_postmeta AS `pm1` ON pm1.post_id=ID AND pm1.meta_key="Amazon.com"
  LEFT JOIN wp_postmeta AS `pm2` ON pm2.post_id=ID AND pm2.meta_key="Amazon.co.uk"
WHERE 
  post_type="post" 
  AND post_status="publish"
GROUP BY
  ID;

Let me know if there are any problems.

Hope this helps,

This doesn't give the desired output. Not sure there is a need for GROUP_CONCAT on the meta_value fields. Each meta_key only holds 1 value. — dean2020, Mar 16 '19 at 22:02

combine two select statements (append columns)

2 Answers2