Gibt es in MySQL eine Möglichkeit, Daten auszuwählen, wenn zwei oder mehr Spalten verschiedener Tabellen gleich sind?

Ich habe eine Localhost-WordPress-Site erstellt und einige zusätzliche Tabellen zu MySQL phpMyadmin hinzugefügt. Jetzt versuche ich, eine JSON-API zu erstellen, die Daten aus all diesen Tabellen abruft. Die Haupttabellen sind wp_posts und wp_users die von WordPress automatisch erstellt wurden. Was ich nicht erreichen kann, ist, die Daten basierend auf dem Ergebnis einer von mir erstellten Tabelle abzurufen (wp_activities). Zum Beispiel, wenn act_module_id Spalte der wp_activities Tabelle ist gleich 1, dann gehe zu Tabelle wp_photos und rufen Sie das Bild ab, dessen Post-ID gleich ist photo_post_id = wp_posts.ID oder wenn gleich 2, dann rufen Sie einfach den Inhalt des Beitrags ab wp_posts. Unten ist, was ich versucht habe:

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;

Wie Sie also sehen können, versuche ich zu überprüfen, was die Modul-ID ist wp_activities und hängt davon ab, die Daten aus den restlichen Tabellen abzurufen, damit das Ergebnis dieser IF-Anweisung an eine aufgerufene Variable zurückgegeben wird activities_module. Wenn Sie weitere Informationen benötigen, lassen Sie es mich bitte wissen. Ich bin neu bei MySQL und versuche zu verstehen, wie es funktioniert. Jede Antwort wird geschätzt!

BEARBEITEN: Antwort von @Jorge Campos:

<<<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: Ich füge den gesamten API-Dateicode hinzu:

<?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);
?>

BEARBEITEN 3: @Jorge Campos, wenn ich eine weitere Tabelle hinzufüge, wird mir erneut derselbe Fehler ausgegeben (leeres Ergebnis im JSON). Überprüfen Sie den folgenden Code:

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

$result = [];
$sql = <<<SQL
SELECT 
    wp_users.*,
    wp_usermeta.*,
    wp_posts.*,
    wp_photos.*,
    wp_activities.*,
    wp_videos.*,
    
    CASE WHEN wp_activities.act_module_id = 1 AND wp_photos.pho_post_id = wp_posts.ID
    THEN wp_photos.pho_file_name
    WHEN wp_activities.act_module_id = 5 AND  wp_videos.vid_post_id = wp_posts.ID
    THEN wp_videos.vid_url
    END as content
    
FROM wp_users
INNER 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_id = 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'],
        'post_modified' => $row['post_modified'],
        'post_content' => $row['content']
        
    ];
}
echo json_encode($result, JSON_PRETTY_PRINT);
?>

Ich antworte von meinem Telefon aus, also bleib hier mit mir. Die Lösung besteht darin, einen linken Join zu verwenden, etwa so:

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

BEARBEITEN: Basierend auf Ihrer Bearbeitung müssen Sie es hier beheben:

<<<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: Basierend auf Ihrem PHP-Code finden Sie hier einige Beispiele, wie Sie das Problem beheben können.

<?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);
?>