PHP PDO 在同一查询中查询两个表

Question

我有这个查询

$sth = $db->prepare(
    'SELECT shout_id FROM shout_info'
    . ' WHERE shout_location = "3"'
    . ' AND (shout_sex = "0" OR shout_sex = ?)'
    . ' AND (shout_age = "0" OR shout_age = ?)'
    . ' AND shout_location_spec = ? AND user_id <> ?'
    . ' ORDER BY date_created DESC LIMIT 15'
);

        $sth->bindValue(1, $user_sex);
        $sth->bindValue(2, $sql_general_age);
        $sth->bindValue(3, $user_workplace);
        $sth->bindValue(4, $user_id);
        $sth->execute();
        $workplace_array = $sth->fetchAll(PDO::FETCH_ASSOC);

它很长，但我想知道如何从另一个表中的列进行检查，检查将是ANDshout_approved =“1”但是shout_approved在表shout_status中， shout_status 的主要 id 是shout_id，就像在shout_info 中一样，所以我不确定如何使用与原始查询中检查的相同id 来检查此表。抱歉，我试图尽可能清楚地解释。

如果您还需要什么，请告诉我

Answer 1

为此，您需要一个LEFT OUTER JOIN:

SELECT
    s.shout_id
FROM
    shout_info s
        LEFT OUTER JOIN
            shout_status ss
        ON
            ss.shout_id = s.shout_id
WHERE
    s.shout_location = "3" AND
    (s.shout_sex = "0" OR s.shout_sex = ?) AND
    (s.shout_age = "0" OR s.shout_age = ?) AND
    s.shout_location_spec = ? AND
    s.user_id <> ? AND
    ss.shout_approved = "1"
ORDER BY
    s.date_created DESC
LIMIT
    15