我可以得到一些MySQL JOIN的帮助吗?
基本上我有4个表,traders, streetaccounts(与一个交易者关联),recommation_brokerages和recommendations。我需要从交易者表中获得所有交易者的姓名和电子邮件地址,其中交易者的街道账户。Brokerage_id存在于推荐表和推荐表中。
这是我的表的基本结构。
tbl_traders
--------------------------------------
trader_id | trader_name | email
--------------------------------------
tbl_streetaccounts
--------------------------------------
trader_id | brokerage_id
--------------------------------------
tbl_recommendation_brokerages
--------------------------------------
recommendation_id | brokerage_id
--------------------------------------
tbl_recommendations
--------------------------------------
recommendation_id | published
--------------------------------------
select t.* from tbl_traders t
inner join tbl_streetaccounts s on t.trader_id = s.trader_id
inner join tbl_recommendation_brokerages rb on s.brokerage_id = rb.brokerage_id
inner join tbl_recommendations r on t.recommendation_id = r.recommendation_id
注意,通常认为使用描述类型的前缀是"无用的"。这种80年代的做法经常受到批评。
请参阅Joel http://www.joelonsoftware.com/articles/Wrong.html在这篇文章中的"我很饿"部分
假设tbl_recommendation_brokerages.recommendation_id
是tbl_recommendations.recommendation_id
的外键,我们不需要加入tbl_recommendations
。
SELECT
t.trader_name,
t.email
FROM
tbl_traders t
WHERE
EXISTS (
SELECT
*
FROM
tbl_streetaccounts acct,
tbl_recommendation_brokerages rec
WHERE
acct.brokerage_id = rec.brokerage_id
AND acct.trader_id = t.trader_id
)