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

What have you got so far? (and you should have expected this comment after more than 30 questions) — Eddy
– Eddy, Commented Aug 20, 2011 at 17:32
You should post the full definition of your tables especially the primary and foreign keys. We're just having to guess at the relationships. — cope360
– cope360, Commented Aug 20, 2011 at 17:40

Hogan · Accepted Answer · 2011-08-20 17:32:21Z

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

answered Aug 20, 2011 at 17:32

Hogan

70.7k10 gold badges83 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

cope360 Over a year ago

Won't this return the trader multiple times if they have multiple street accounts?

Hogan Over a year ago

@cope360 Yep it will. A distinct before t.* should fix that. Would be faster than a sub-query.

Lucero Over a year ago

Faster than a subquery? My experience is that a NOT IN (SELECT ... WHERE ...) is usually faster, if the subquery does not reference rowdata (so that it is evaluated once for all rows, and not once per row).

Hogan Over a year ago

Unless the optimizer is super-human (DB2?) Joins are faster than a subquery. (see mysql's own doc: dev.mysql.com/doc/refman/5.1/en/rewriting-subqueries.html)

Carl Weis Over a year ago

Just have to do add the DISTINCT keyword and this query works for me! Yeah I hate table or even variable prefixes, this is an existing project that I had to pick up and complete. Nothing like working through somebody else's code... Thanks for the help!

cope360 · Accepted Answer · 2011-08-20 17:38:07Z

0

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
        )

answered Aug 20, 2011 at 17:38

cope360

6,3742 gold badges22 silver badges34 bronze badges

Collectives™ on Stack Overflow

Help with mysql join

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related