...

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



jenny.s
05-30-2011, 08: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!



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum