使用PHP + MySQL左连接来组合来自2个查询的结果

Question

Here's where I'm getting stuck. I have two tables that I'm trying to pull information from. One table contains data of interest under the idand post_title columns, the other contains data under the post_id, meta_key, and meta_value columns.

I have an array of numbers stored in $in, by which I can filter the data from both tables.

To return the id and post_title from the first table is a simple:

$query = "SELECT id, post_title FROM wplz_posts WHERE id IN ($in)";

This of course returns a name and a unique ID for that name, for example:

[id] => 8 
[post_title] => Rustic Wooden Chair

I however also want to grab a price associated with the id returned above, and a single query to do that would look something like:

$query = "SELECT meta_value 
FROM wplz_postmeta 
WHERE post_id IN ($in) AND meta_key = '_price'";

My issue is that I'd like to be able to return all of this data to a single array with one query instead of two, that way both the post_title and the meta_value will correspond to each single number in $in (say, 8) which will then allow me to return the query to an array and then loop through that array as needed. I honestly have spent quite a bit of time on this and I think what I am trying to do requires a "LEFT JOIN", but after multiple videos and tutorials I haven't been able to figure out how to make this elusive technique work. Thank you for your help. :)

Answer 1

You would want to do an INNER JOIN that looks like this:

$query = "SELECT p.id, p.post_title, pm.meta_value
FROM wplz_posts p
INNER JOIN wplz_postmeta pm ON pm.post_id=p.id
WHERE p.id IN ($in)
AND pm.meta_key = '_price'";

You need to join on the id and post_id keys but also filter the join based on the meta_key value which is _price