I want to run a left join query on Wordpress postmeta table. The table looks like this:
Code:
-------------------------------------
post_id |  meta_key   | meta_value   |
-------------------------------------
   1    | project_id  |    1         |
-------------------------------------
   1    |  name       |    cat      |
-------------------------------------
   1    |  desc        |    cc       |
-------------------------------------
   2    | project_id  |    2         |
-------------------------------------
   2    |  name       |    dog      |
-------------------------------------
   2    |  desc        |    dd       |
-------------------------------------
I will use the query result as an input for a function, so the output formate of this query looks exactly like this:
Code:
array(2) {
  [0]=>
  object(stdClass)#1 (3) {
    ["ID"]=>
    string(1) "2"
    ["name"]=>
    string(18) "cat"
    ["desc"]=>
    string(4) "cc"
  }
  [1]=>
  object(stdClass)#2 (3) {
    ["ID"]=>
    string(1) "1"
    ["name"]=>
    string(19) "dog"
    ["desc"]=>
    string(7) "dd"
  }
}
The above result comes from this query that runs on multi-table:
Code:
$query = <<<QUERY
SELECT 	DISTINCT(u.ID),
		u.user_email,
		u.user_nicename,
		u.display_name,
		pd.value	
FROM $wpdb->users u 
LEFT
JOIN {$bp->profile->table_name_data} pd 
		ON u.ID = pd.user_id
LEFT 
JOIN (
SELECT DISTINCT( p.post_author ) ID
FROM $wpdb->posts p
WHERE
p.post_type = 'sp_reg'
AND p.post_parent = $postid
$for_admin
) pa
ON u.ID = pa.ID
WHERE 
u.user_status = 0
AND pa.ID IS NULL
AND pd.field_id = 1
ORDER BY pd.value ASC
QUERY;
Would anybody please help me to modify the above query so that it can run on my single table to get the same formate of result?
Thanks!