CODEWITHSUNDEEP
mysqljoin
Bono
Bono

Reputation: 4869

Join on multiple ids

I've been helping a friend out with some of his MySQL queries, and came up with this query. While writing this I kind of wondered if there wasn't a better way to do this:

select offers.*, users.*, items.* from offers 
join users on users.id = offers.sender_id
join items on 
    (items.id = offers.receiver_gets_1)
or
    (items.id = offers.receiver_gets_2)
or
    (items.id = offers.receiver_gets_3)
or
    (items.id = offers.receiver_gets_4)
or
    (items.id = offers.sender_gets_1)
or
    (items.id = offers.sender_gets_2)
or
    (items.id = offers.sender_gets_3)
or
    (items.id = offers.sender_gets_4)
where receiver_id = 1;

It kind of made me feel bad, because I really have the feeling I gave him some messed up query (not that he minds). It has left me wondering if there isn't a nicer way to write this down?

I tried looking on google for stuff like these questions:
- Mysql Left Join on multiple conditions
- MySQL how to join tables on two fields
- https://dba.stackexchange.com/questions/33996/which-is-better-many-join-conditions-or-many-where-conditions

But these didn't really offer any other way. I did run in to another question where the answer was set-up the same as my own query.

I know the database could perhaps modeled better, but it's not my database to toy with. I don't really agree with how his tables are built up or anything, but the question remains interesting to me non the less.

Upvotes: 1

Views: 11050

Answers (1)

Barmar
Barmar

Reputation: 782785

You can use IN:

select offers.*, users.*, items.* from offers 
join users on users.id = offers.sender_id
join items on 
    items.id IN (offers.receiver_gets_1, offers.receiver_gets_2, offers.receiver_gets_3, offers.receiver_gets_4, 
                 offers.sender_gets_1, offers.sender_gets_2, offers.sender_gets_3, offers.sender_gets_4)

However, it's generally better to design your schema so you don't have repeated columns like receiver_gets_1, receiver_gets_2, .... Instead, you should have a relation table where each if these is in a different row, then you just need to join with it once to get all the matches.

Upvotes: 7

Related Questions