Select distinct values from two tables in wordpress (woocommerce)

Question

I'm trying to:

Select distinct (to ID) ID (post identification) and post_title from wp_posts where post_status = publish and post_type = product;
Select distinct (to post_id) post_id and meta_value from wp_postmeta;

I need to do this with one select statement, so I've tried as many as I'd imagine and search for. The last I tried was this one:


    SELECT a.ID,a.post_title,b.post_id,b.meta_value FROM 
    (
        SELECT DISTINCT ID FROM wp_posts
        UNION
        SELECT DISTINCT post_id FROM wp_postmeta
    ) x LEFT JOIN
    wp_posts a ON x.ID = a.ID LEFT JOIN
    wp_postmeta b ON x.post_id = b.post_id

But, when I try, it tells me:

#1054 - Unknown column 'x.post_id' in 'on clause'

I'd not imagine anything, would someone help me here?

---- edit ----

@Rahul,

This is good, but still returning non distinct values, I'm using this, (note, common columns: a.ID = yy.post_id):

    SELECT a.ID, a.post_title, b.post_id, b.meta_value FROM wp_posts a
    LEFT JOIN (

        SELECT DISTINCT ID
        FROM wp_posts
    )xx ON a.ID = xx.ID
    LEFT JOIN (

        SELECT DISTINCT post_id
        FROM wp_postmeta
    )yy ON a.ID = yy.post_id
    LEFT JOIN wp_postmeta b ON b.post_id = yy.post_id
    WHERE a.post_type = 'product'
    AND a.post_status = 'publish'
    LIMIT 0 , 30

It's return:

ID post_title post_id meta_value

141 Pastel de Teste 141 1

141 Pastel de Teste 141 1436921168:1

141 Pastel de Teste 141 123

141 Pastel de Teste 141 visible

After UNION takes place there is only one table with a single column. This column is named after the name of the column of the first table participating in the UNION, i.e. ID. Hence there is no post_id, you should use x.ID instead. — Giorgos Betsos
@GiorgosBetsos I've tried it, doens't gave me errors. Nice, but still returning non distinct values in the query. Do you see any error? — Rafael Silvério
I have "ID" and "post_id", both are the post identification. The other columns are different. — Rafael Silvério

Rahul Rahul · Accepted Answer · 2015-09-07T19:15:46

Yes because there is no post_id column in your subselect result set. You need to change your query and separate them like

SELECT a.ID,a.post_title,b.post_id,b.meta_value FROM
wp_posts a LEFT JOIN 
( SELECT DISTINCT ID FROM wp_posts ) xx ON a.ID = xx.ID
LEFT JOIN ( SELECT DISTINCT post_id FROM wp_postmeta ) yy 
ON a.some_common_column = yy.some_common_column
LEFT JOIN wp_postmeta b ON b.post_id = yy.post_id;

EDIT:

Per your edited post; You need get the rownumber and get the non duplicate one like

SELECT ID, post_title, post_id, meta_value
FROM ( 
SELECT a.ID,
a.post_title,
b.post_id,
b.meta_value,
(@row_number:=@row_number + 1) AS rownum 
FROM wp_posts a
JOIN (SELECT @row_number:=0) r 
LEFT JOIN wp_postmeta b ON a.ID = b.post_id ) xxx
WHERE rownum = 1;

Select distinct values from two tables in wordpress (woocommerce)

1 Answers