具有反向连接的MySql查询

Question

I am trying to build a query returning a list of contacts associated with a specific opportunity.

I have 3 tables: contacts, opportunities, and relationships (many to many)

contacts

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

opportunities

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

relationships

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

Now, if I have the opportunity_id, I want to return all the contacts associated with that opportunity.

So if opportunity_id=4, the result of the successful query should be:

Hillary CLinton
Tiger Woods

But this is my query, which only returns 1 record:

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

I am stuck on how to do flip-flop the joins in this query.

EDIT: I just discovered UNION and then tried this and it seems to work:

(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' ) ) ) )

But this seems clunky. Is this the best way to handle this?

Answer 1

In a simple way, you need to change the design of your table relationships, I'll suggest:

relationships

id     id_contact     id_opportunity
-------------------------------------
1      1              1
2      3              3
3      4              4
4      3              4

So with the changes on relationships, your query can look like:

SELECT contacts.name 
FROM contacts
INNER JOIN relationships ON contacts.id = relationships.id_contact
INNER JOIN opportunities ON opportunities.id = relationships.id_opportunity
WHERE opportunities.id=4;

You must remember to normalize your tables (so the table relationships must have an identifier from the other tables, just one from each table... thats why is an identifier), and be more careful in the many-to-many tables.