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. :)
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
If there is exactly 1 row on each side of the join you're looking for an INNER JOIN, not LEFT JOIN.
SELECT id, post_title,meta_value FROM wplz_posts
INNER JOIN wplz_postmeta
ON wplz_postmeta.post_id=wplz_posts.id
WHERE wplz_posts.id IN ($in)
AND meta_key = '_price'
But you can adjust the nature of JOIN if there can be cases where only 1 table has matching records
You are correct that you can use a left join. This query will return the joined tables. If there are no rows in the meta table that matches the post, $array['meta_value] will be null. If there are several rows that match, the id and post_title will be duplicated.
If you know beforehand that every post has at least one meta value you can use an inner join instead.
SELECT t1.id, t1.post_title, t2.meta_value
FROM wplz_posts AS t1
LEFT JOIN wplz_postmeta AS t2 ON t1.id = t2.post_id