Is there a way in MySQL to select data if two or more columns of different tables are equal?-CodePudding

I have created a localhost WordPress site and I have added some extra tables on MySQL phpMyadmin. Now I am trying to make a JSON API that will retrieve data from all those tables. The main tables are wp_posts and wp_users that were automatically created by WordPress. What I can't achieve is to retrieve the data based on the result of a table I have created (wp_activities). For example, if act_module_id column of the wp_activities table is equal to 1 then go to table wp_photos and retrieve the image that has post id equals to photo_post_id = wp_posts.ID or else if equals to 2 then just retrieve the post content from wp_posts. Below is what I have tried:

SELECT * 
FROM wp_users
INNER JOIN wp_posts ON wp_users.ID = wp_posts.post_author AND IF(wp_activities.act_module_id = 1) THEN wp_photos.photo_post_id ELSE IF(wp_activities.act_module_id = 2) THEN wp_posts AS activities_module AND wp_posts.post_type = 'post'
INNER JOIN (
    SELECT user_id,
           MAX(IF(meta_key = 'first_name', meta_value, NULL)) AS first_name,
           MAX(IF(meta_key = 'last_name', meta_value, NULL)) AS last_name,
           MAX(IF(meta_key = 'avatar_hash', meta_value, NULL))AS avatar_hash
    FROM wp_usermeta
    GROUP BY user_id
) pivoted ON pivoted.user_id = wp_users.ID
ORDER BY wp_posts.ID
SQL;

So, as you can see what I am trying to do is to check what is the module id of wp_activities and depends on that to retrieve the data from the rest of the tables, so the result of this IF statement to return on a variable called activities_module. If there is any other information you need please let me know. I am new to MySQL and I am trying to understand how it works. Any answer will be appreciated!

EDIT: @Jorge Campos answer:

<<<SQL
SELECT *
FROM wp_users
CASE WHEN wp_activities.act_module_id = 1 
  THEN wp_photos.pho_post_id
  ELSE wp_posts.post_content
  END
LEFT JOIN wp_posts ON wp_users.ID = wp_posts.post_author AND wp_posts.post_type = 'post'

INNER JOIN (
    SELECT user_id,
           MAX(IF(meta_key = 'first_name', meta_value, NULL)) AS first_name,
           MAX(IF(meta_key = 'last_name', meta_value, NULL)) AS last_name,
           MAX(IF(meta_key = 'avatar_hash', meta_value, NULL))AS avatar_hash
    FROM wp_usermeta
    GROUP BY user_id
) pivoted ON pivoted.user_id = wp_users.ID
ORDER BY wp_posts.ID
SQL;

EDIT2: I add the whole API file code:

<?php
header("Content-Type: application/json");
require_once 'wp-connect.php';

$result = [];
$sql = <<<SQL
SELECT 
    wp_users.*,
    pivoted.*,
    CASE WHEN wp_activities.act_module_id = 1
       THEN wp_photos.pho_post_id
       ELSE wp_posts.post_content
    END
FROM wp_users
   LEFT JOIN wp_posts ON wp_users.ID = wp_posts.post_author 
      AND wp_posts.post_type = 'post'   
   LEFT JOIN wp_photos on wp_posts.ID = wp_photos.pho_post_id
   INNER JOIN (
    SELECT user_id,
           MAX(IF(meta_key = 'first_name', meta_value, NULL)) AS first_name,
           MAX(IF(meta_key = 'last_name', meta_value, NULL)) AS last_name,
           MAX(IF(meta_key = 'avatar_hash', meta_value, NULL))AS avatar_hash
    FROM wp_usermeta
    GROUP BY user_id
    ) pivoted ON pivoted.user_id = wp_users.ID
INNER JOIN wp_activities on wp_activities.act_owner_user = wp_users.ID
ORDER BY wp_posts.ID
SQL;
foreach (mysqli_query($conn, $sql) as $row) {
    $result[] = [
        'post_id' => $row['ID'],
        'user_id' => $row['post_author'],
        'post' => $row['post_content'],
        'firstName' => $row['first_name'],
        'lastName' => $row['last_name'],
        'username' => $row['user_nicename'],
        'avatar' => $row['avatar_hash'],
        'post_date' => $row['post_date'],
        'post_type' => $row['post_type'],
        'post_title' => $row['post_title'],
        'post_name' => $row['post_name']
    ];
}
echo json_encode($result, JSON_PRETTY_PRINT);
?>

CodePudding user response：

I'm answering from my phone, so bare with me here. The solution is to use a left join, something like this:

select 
...
case when wp_activities.act_module_id = 1 
  then wp_photos.photo_post_id
  else wp_posts.columnHere end 
...
from wp_users
left join wp_posts on wp_users.ID = wp_posts.post_author
left join wp_photos on wp_users.ID = wp_photos.photo_post_id
 -- please complete the rest of the query

EDIT: Based on your edit here is where you need to fix it:

<<<SQL
SELECT 
-- here in the select section is where you 
-- get what you want based on the condition
-- so you can still use the * but it would be better to be
-- a little bit more strict with columns from which table like:
    wp_users.*,
    pivoted.*,
    CASE WHEN wp_activities.act_module_id = 1 
       THEN wp_photos.pho_post_id
       ELSE wp_posts.post_content
    END
FROM wp_users
   LEFT JOIN wp_posts ON wp_users.ID = wp_posts.post_author 
      AND wp_posts.post_type = 'post'   
   LEFT JOIN wp_photos on wp_users.ID = wp_photos.photo_post_id
   INNER JOIN (
    SELECT user_id,
           MAX(IF(meta_key = 'first_name', meta_value, NULL)) AS first_name,
           MAX(IF(meta_key = 'last_name', meta_value, NULL)) AS last_name,
           MAX(IF(meta_key = 'avatar_hash', meta_value, NULL))AS avatar_hash
    FROM wp_usermeta
    GROUP BY user_id
    ) pivoted ON pivoted.user_id = wp_users.ID
-- YOU STILL NEED TO ADD A JOIN WITH wp_activities here 
-- since I dont' know your model I will add it here as a suggestion 
-- and you can correct it based on your model
INNER JOIN wp_activities on wp_activities.activity_user = wp_users.ID
ORDER BY wp_posts.ID
SQL;

EDIT2: Based on your PHP code, here is a few examples on how to fix it.

<?php
header("Content-Type: application/json");
require_once 'wp-connect.php';

$result = [];
$sql = <<<SQL
SELECT 
// you need to be explicit on the column names and 
// and where they should be coming from here at this section
// that is the SELECT section (anything between select and from)
// pay attention here because I do not know your column names
// so I'm simply using something for you to understand
    wp_users.userName as post_author,
    pivoted.first_name,
    pivoted.last_name,
    pivoted.user_nicename,
    pivoted.avatar_hash,
//since you are getting data from two possible tables you have to 
//have the same rule for the columns you are selecting from each
//table, because your PHP code down there is assuming everything
//is coming from the wp_posts table when it could not be the case
// if wp_activities.act_module_id IS NOT 1 so:
    CASE WHEN wp_activities.act_module_id = 1
       THEN wp_photos.pho_post_id
       ELSE wp_posts.ID
    END as contentID,

    CASE WHEN wp_activities.act_module_id = 1
       THEN wp_photos.pho_post_id
       ELSE wp_posts.post_content
    END as content,
// here you need above condition and alias (the: as something part)
// to all below columns that you added on php code
// 'post_date' => $row['post_date'],
// 'post_type' => $row['post_type'],
// 'post_title' => $row['post_title'],
// 'post_name' => $row['post_name']

FROM wp_users
   LEFT JOIN wp_posts ON wp_users.ID = wp_posts.post_author 
      AND wp_posts.post_type = 'post'   
   LEFT JOIN wp_photos on wp_posts.ID = wp_photos.pho_post_id
   INNER JOIN (
    SELECT user_id,
           MAX(IF(meta_key = 'first_name', meta_value, NULL)) AS first_name,
           MAX(IF(meta_key = 'last_name', meta_value, NULL)) AS last_name,
           MAX(IF(meta_key = 'avatar_hash', meta_value, NULL))AS avatar_hash
    FROM wp_usermeta
    GROUP BY user_id
    ) pivoted ON pivoted.user_id = wp_users.ID
INNER JOIN wp_activities on wp_activities.act_owner_user = wp_users.ID
ORDER BY wp_posts.ID
SQL;
foreach (mysqli_query($conn, $sql) as $row) {
    $result[] = [
        'post_id' => $row['contentID'],
        'user_id' => $row['post_author'],
        'post' => $row['content'],
        'firstName' => $row['first_name'],
        'lastName' => $row['last_name'],
        'username' => $row['user_nicename'],
        'avatar' => $row['avatar_hash'],
        'post_date' => $row['post_date'],
        'post_type' => $row['post_type'],
        'post_title' => $row['post_title'],
        'post_name' => $row['post_name']
    ];
}
echo json_encode($result, JSON_PRETTY_PRINT);
?>