Help with mysql join

Question

Can I get some help with a MySQL JOIN?

Basically I have 4 tables, traders, streetaccounts (that are associated with a trader), recommendation_brokerages and recommendations. I need to get all the traders names and email addresses from the traders table, where the traders streetaccount.brokerage_id exists in the recommendation_brokerages table and in the recommendations table.

Here is the basic structure of my tables.

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
--------------------------------------

Answer 1

Assuming tbl_recommendation_brokerages.recommendation_id is a foreign key to tbl_recommendations.recommendation_id, we don't need to join to 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
        )

Answer 2

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

NB, it is generally considered "not useful" to have a prefix that describes type. This practice from the 80s has been often criticized.

See the section "I'm hungary" in this post by Joel http://www.joelonsoftware.com/articles/Wrong.html