PDA

View Full Version : How to run a left join query on a single table?



jenny.s
05-30-2011, 09:23 AM
I want to run a left join query on Wordpress postmeta table. The table looks like this:

-------------------------------------
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:

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:

$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!