John Doe John Doe - 1 year ago 62
MySQL Question

Using PHP + MySQL Left Join to combine results from 2 queries

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

columns, the other contains data under the
, and

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

To return the
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
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
and the
will correspond to each single number 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 Source

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

$query = "SELECT, p.post_title, pm.meta_value
FROM wplz_posts p
INNER JOIN wplz_postmeta pm ON
WHERE 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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download