Join three tables

You did not give the full table name of the favorites table, but assuming that it uses the same prefix as the standard WP tables, I think this is what you want:

$sql = "
SELECT * FROM $wpdb->posts p
JOIN $wpdb->users u ON (p.ID = u.post_author)
JOIN {$wpdb->prefix}favorites f ON (p.ID = f.post_id)
WHERE p.post_type = 'post'
   AND p.post_status = 'publish'
";

Hi vtxyzzy, many thanks for your response and apologies for the delay – I was away.

I sorted it before I saw your response but you are very much on point. Many thanks again. I hope this will help someone else in the future.

I have run into another problem however; the query above returns something similar to this:

+---------------+-----------------+
| Type          |  Price          |
+---------------+-----------------+
| Music         |  19.99          |
| Music         |   3.99          |
| Music         |  21.55          |
| Toy           |  89.95          |
| Toy           |   3.99          |
+---------------+-----------------+

My problem is how to group the products by type, so that it returns a single product type and total price for each product type e.g:

Music | 45.53
Toy | 93.94 and so on.

Once again, many thanks

Cheers!

If you are using a foreach loop to display the results, something like this should work:

$total = 0;
$type = '';
$currtype = '';
foreach ($results as $post){
   if ($type == '') $currtype = $post->type;
   if ($type != $currtype) {  // Is this a new type?
      // Print the line for the currtype
      $total = 0; // Reset for this type
      $currtype = $type;
   }
   $total += $post->price;
}
// Print the line for the currtype

Of course, the variable names are probably not what you used, but I hope you can get the idea.

Thanks for the response vtxyzzy, unfortunately, it hasn’t worked; i.e. It doesn’t group and add the prices.

Since I can’t see your code, I can only suggest a general solution. If you will put your code into a pastebin and post a link to it here, I may be able to give more specific help.