John Doe John Doe - 2 months ago 7
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

id
and
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

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

Comments