How to run a left join query on postmeta table?

You are only asking for project_id.post_id so you are only going to get that post_id. You need to ask for all the fields you want returned.

SELECT project_id.post_id, height.height, width.width....

ok,just now I changed the first 2 lines to:

SELECT project_id.post_id, height.height, width.width
FROM $wpdb->postmeta project_id,$wpdb->postmeta height,$wpdb->postmeta width

var_dump returns array(0) { }
(if don’t add height and width table in FROM line, the same 0 return )

Please give further instruction!

Check the error response, you don’t need modify line two as you have done. You can already SELECT height.* and width.* from your LEFT JOINs

Yes. GRAC is correct. You don’t need to list your tables like that in the ‘FROM’ clause. The joins are enough to bring those tables into the query, and you could end up with naming conflicts if you try to do both.

Also, you should make sure that you are using valid table and column names. I guessed at those values.

It might help to echo your MYSQL errors, if you aren’t already.

I put the 2 lines in template file, right below the line I echo the query result, it prints nothing.

<?php $wpdb->show_errors(); ?>
<?php $wpdb->print_error(); ?>

I changed the 2nd line of the query to FROM $wpdb->postmeta project_id, still no error messages and returns project_id, This line might be the problem, coz it’s the project_id, not contains height and width info.
Here’s how they look like in postmeta table:

-------------------------------------
post_id |  meta_key   | meta_value   |
-------------------------------------
   1    | project_id  |    1         |
-------------------------------------
   1    |  height     |    50        |
-------------------------------------
   1    |  width      |    100       |
-------------------------------------
   2    | project_id  |    2         |
-------------------------------------
   2    |  height     |    50        |
-------------------------------------
   2    |  width      |    100       |
--------------------------------------

Edit:
Now I changed the 1st line to

SELECT  project_id.post_id, project_id.meta_value,project_id.meta_key

got this:

array(3) {
  [0]=>
  object(stdClass)#101 (3) {
    ["post_id"]=>
    string(3) "1"
    ["meta_value"]=>
    string(3) "1"
    ["meta_key"]=>
    string(7) "project_id"
  }
  [1]=>{...}
  [2]=>{...}
}

Seems, the “FROM $wpdb->postmeta project_id ” is wrong.
But, “FROM $wpdb->postmeta” deosn’t give any result.

New finding- if I change the first 2 lines to:

SELECT  project_id.post_id, height.meta_value,width.meta_value
FROM $wpdb->postmeta project_id

The output will show:

array(3) {
  [0]=>
  object(stdClass)#101 (3) {
    ["post_id"]=>
    string(3) "1"
    ["meta_value"]=>
    string(3) "50"
  }
  [1]=>{...}
  [2]=>{...}
}

After serveral test, I see it can only output 3 items in one object (post_id, meta_key, meta_value)
It takes whatever value (height,width…)I specific in SELECT line, but each object can only have one post_id, one meta_key and one meta_value.
So, now the problem is a bit clear– my code makes the query result structured the same as postmeta table, to get one id with all related height/width…, I need another structure.
Please help!

@apljdi,
Could you help check this code? It gets the correct result but from multi tables, I don’t understand what is pd, pa, and u. / p. , hope there’s a way I can borrow this code to apply on postmeta 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 = 'ep_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;

pd, pa, and u. / p.

Those are table ‘aliases’. Look at this part of the code:

FROM $wpdb->users u

That is the same as, and can also be written as:

FROM $wpdb->users AS u

Now, within the query you can refer to the wp_users as ‘u’.

I see.
Thanks for helping! I will try to see if I can apply this code on postmeta table or not.

@apljdi
Seems there’s no way to query colums as keys in postmeta table. I decide to move on in other ways.

Somedays ago, I learned get_post_meta from you. Now I do it this way:

function mp_all_ids( ){
global $wpdb, $post;
$query = <<<QUERY
SELECT      project_id.post_id
FROM        $wpdb->postmeta project_id
WHERE       project_id.meta_key = 'project_id'
ORDER BY    project_id.meta_value+(0) ASC
QUERY;

$postids = $wpdb->get_col( $query );

foreach ($postids as $id) {
    $meta=get_post_custom($id);

	$data = Array(
	'ID' => $meta['project_id'][0],
	'height' => $meta['height'][0],
	'width' => $meta['width'][0]
	);
}
return apply_filters( 'mp_all_ids', $data );
}

From var_dump($data), this function get only one result(because of Foreach) like this:

array(3) {
  ["ID"]=>
  string(3) "463"
  ["height"]=>
  string(16) "100"
  ["width"]=>
  string(32) "50"
}

How can I get all projects with data like this:

array(2) {
  [0]=>
  object(stdClass)#1 (3) {
    ["ID"]=>
    string(1) "2"
    ["height"]=>
    string(3) "100"
    ["width"]=>
    string(2) "50"
  }
  [1]=>
  object(stdClass)#2 (3) {
    ["ID"]=>
    string(1) "1"
    ["height"]=>
    string(3) "200"
    ["width"]=>
    string(3) "100"
  }
}

Please help!

$data[] = ...

Great! Got all projects by $data[] = …

Still need one more tip to complete-
Now, each project is an array, how to get it as object(stdClass){…} ?

The array comes from this:
$data[] = Array(...);
If I change to $data[] = object(…), then , the “=>” is wrong.

@apljdi,

Complete! $data[] = (object)Array(….);

Thanks so much for your helping!