how to see result in one row? --my id repetition in two different row---CodePudding

(SELECT
     id, post_date
     FROM mln_posts WHERE post_type = 'shop_order')
UNION
(SELECT
     post_id, meta_value
     FROM mln_postmeta WHERE meta_key = '_order_total')
ORDER BY id

enter image description here

CodePudding user response：

wrap your query and then use GROUP BY


SELECT * FROM 
(SELECT 
id, 
post_date 
FROM 
mln_posts 
WHERE post_type = 'shop_order'
UNION 
SELECT 
post_id, 
meta_value 
FROM 
mln_postmeta 
WHERE 
meta_key = '_order_total'
) final
GROUP BY id
ORDER BY id

CodePudding user response：

Here's the trick, will create an imaginary column for you to join the 2nd subquery.

SELECT t1.id, t1.post_date, t2.post_id, t2.meta_value
(SELECT
     id, post_date, 1 as col
     FROM mln_posts WHERE post_type = 'shop_order'
     ORDER BY id DESC LIMIT 1) t1
LEFT JOIN 
    (SELECT
    post_id, meta_value, 1 as col
    FROM mln_postmeta WHERE meta_key = '_order_total'
    ORDER BY id DESC LIMIT 1) t2 ON t2.col = t1.col