gpt4 book ai didi

php - 具有反向连接的 MySql 查询

转载 作者:行者123 更新时间:2023-11-29 12:36:47 26 4
gpt4 key购买 nike

我正在尝试构建一个查询,返回与特定机会关联的联系人列表。

我有 3 个表:联系人、机会和关系(多对多)

联系人

id   name
---------
1 Davey Jones
2 Bob Hope
3 Tiger Woods
4 Hillary Clinton

机会

id    description
------------------
1 visit the locker
2 singing and dancing
3 playing golf
4 laughing and crying

关系

id     firstid     firsttype      secondid     secondtype
---------------------------------------------------------
1 1 contact 1 opportunity
2 3 opportunity 3 contact
3 4 contact 4 opportunity
4 4 opportunity 3 contact

现在,如果我有 opportunity_id,我想返回与该机会关联的所有联系人。

所以如果opportunity_id=4,则成功查询的结果应该是:

Hillary CLinton
Tiger Woods

但这是我的查询,它只返回 1 条记录:

SELECT
contacts.name
FROM
contacts
INNER JOIN relationships ON contacts.id = relationships.secondid
INNER JOIN opportunities ON opportunities.id = relationships.firstid
where
opportunities.id=4
and (relationships.firsttype='opportunity' and relationships.secondtype='contact')
or (relationships.firsttype='contact' and relationships.secondtype='opportunity')

我不知道如何在这个查询中翻转连接。

编辑:我刚刚发现 UNION,然后尝试了这个,它似乎有效:

(select contacts.name from contacts where contacts.id =
(select secondid as id from relationships where (firstid = 4 and (firsttype='opportunity' and secondTtpe='contact' ) ) ) )
UNION
(select contacts.name from contacts where contacts.id =
(select firstid as id from relationships where (secondid = 4 and (firsttype='contact' and secondtype='opportunity' ) ) ) )

但这看起来很笨拙。这是处理这个问题的最佳方法吗?

最佳答案

试试这个:

SELECT contacts.name FROM contacts
inner join (
SELECT * from relationships
where
(firstid = 4 and (firsttype='opportunity' and secondtype='contact' ) )
or
(secondid= 4 and (firsttype='contact' and secondtype='opportunity' ) )
) rel
on contacts.id = rel.id;

关于php - 具有反向连接的 MySql 查询,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/26659024/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com