SQL: how to search in two different tables, merging the results?

Question

This is a wordpress database and I have 2 tables of interest: posts and postmeta. What I want to do is to get $term(searched by users) and use this variable on the SQL search.

The tables I want to search if there is a match are meta_value(if meta_key is albumYear), post_content and post_title.

The problem is that meta_value is in a different table than the others, but its table postmeta has post_id, which is the same id that posts.ID. This makes possible to know that some meta value belongs to some post.

I tried the following code and I hope I made it clear what I need so I can get help from people here. I really need help on this and I couldn't find the answer on google or here. Thanks in advance.

$query_sql = "
    SELECT post_id, meta_key, meta_value FROM $wpdb->postmeta
    ,
     (SELECT *
     FROM $wpdb->posts
     WHERE NOT wpdb->posts.post_status = 'inherit'
     AND (wpdb->posts.post_type = 'page' OR wpdb->posts.post_type = 'post')
     AND (wpdb->posts.post_content LIKE '%".$term."%' OR wpdb->posts.post_name LIKE '%".$term."%' OR $wpdb->postmeta.meta_value LIKE '%".$term."%')
     ORDER BY wpdb->posts.post_title ASC) x
     WHERE $wpdb->postmeta.post_id = $wpdb->posts.ID AND $wpdb->postmeta.meta_key = 'albumYear'
";

$query_result = $wpdb->get_results($query_sql, OBJECT);

Sample of the SQL structure:

1.wp_posts
1.1 ID, post_type, post_content, post_status, post_name, post_title etc

2.wp_postmeta
2.1 post_id, meta_key, meta_value

So, I need to search matches for $term in meta_value(when meta_key is X), post_content, post_name etc. But also I need to identify the Post im searching and return all of its content.

Nagh Nagh · Accepted Answer · 2012-08-13T00:39:03

All you need is just a join of two tables.

Sorry, don't have WP available atm, so can't check it, but should like something like that:

$query_sql = "
  SELECT 
    post_id, meta_key, meta_value,
    $wpdb->posts.*
  FROM $wpdb->posts
  LEFT JOIN $wpdb->postmeta ON $wpdb->posts.ID = $wpdb->postmeta.post_id
  WHERE NOT wpdb->posts.post_status = 'inherit'
    AND (wpdb->posts.post_type = 'page' OR wpdb->posts.post_type = 'post')
    AND (wpdb->posts.post_content LIKE '%".$term."%' OR 
         wpdb->posts.post_name LIKE '%".$term."%' OR 
         $wpdb->postmeta.meta_value LIKE '%".$term."%')
    AND $wpdb->postmeta.meta_key = 'albumYear'
  ORDER BY wpdb->posts.post_title ASC
";

Add your checks and query fields list if you need it.

SQL: how to search in two different tables, merging the results?

1 Answers