因此,我试图从某人检索所有兴趣,并能够列出它们。这适用于以下查询。

SELECT *,(
    SELECT GROUP_CONCAT(interest_id SEPARATOR ",")
    FROM people_interests
    WHERE person_id = people.id
) AS interests
FROM people
WHERE id IN (
    SELECT person_id
    FROM people_interests
    WHERE interest_id = '.$site->db->clean($_POST['showinterest_id']).'
)
ORDER BY lastname, firstname


在我遇到的麻烦中,我只想选择那些恰好在ID为volleyballplayers的表中具有其ID的用户。该表仅具有一个id,person_id,team_id和日期字段。

SELECT *,(
    SELECT GROUP_CONCAT(interest_id SEPARATOR ",")
    FROM people_interests
    WHERE person_id = people.id
) AS interests
FROM people
WHERE id IN (
    SELECT person_id
    FROM people_interests
    WHERE volleyballplayers.person_id = person_id
)
ORDER BY lastname, firstname


我只想确保仅显示排球运动员表中的人,但是我得到一个错误,说Unknown column 'volleyballplayers.person_id' in 'where clause',尽管我非常确定表的名称,并且我知道该列的名称为person_id。

最佳答案

尝试通过子查询将其加入,

SELECT  *, GROUP_CONCAT(interest_id) interests
FROM    people a
        INNER JOIN people_interests b
            ON b.person_id = a.id
        INNER JOIN
        (
            SELECT DISTINCT person_id
            FROM volleyballplayers
        ) c ON b.person_id = c.person_id
GROUP BY a.id
ORDER BY lastname, firstname

关于php - 将ID与不同表行的ID进行比较mysql,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/12736376/

10-11 15:00