CODEWITHSUNDEEP
sql
Sharks Sri
Sharks Sri

Reputation: 53

SQL - Multiple clauses on JOIN?

Let me post my database structures first. Table: monsterdata - http://prntscr.com/92eet Table: user_team - http://prntscr.com/92eef

I want data in user_team(m1,m2,m3,m4,m5,m6) to be matched with monsterdata.id so that I can retrieve other info. What I managed to do was retrieve only one data

    SELECT * FROM monsterdata LEFT JOIN user_team ON monsterdata.id=user_team.m1

But what I really want to do is, to include m2,m3,m4,m5,m6 to monsterdata.id=user_team.X

This might be silly but I'm more silly not to figure how to do this. Thanks for the help!

Upvotes: 5

Views: 14669

Answers (3)

Michael Buen
Michael Buen

Reputation: 39443

You can use this condition:

SELECT * FROM monsterdata 
LEFT JOIN user_team 
ON monsterdata.id = COALESCE(
    user_team.m1,user_team.m2,user_team.m3,
    user_team.m4,user_team.m5,user_team.m6)

Upvotes: 0

Jonas T
Jonas T

Reputation: 3077

Try this.

SELECT * FROM monsterdata 
LEFT JOIN user_team 
ON (monsterdata.id=user_team.m1
OR monsterdata.id=user_team.m2
OR monsterdata.id=user_team.m3
OR monsterdata.id=user_team.m4
OR monsterdata.id=user_team.m5
OR monsterdata.id=user_team.m6)

Upvotes: 2

Tushar
Tushar

Reputation: 8049

    SELECT * 
    FROM monsterdata LEFT JOIN user_team 
    ON monsterdata.id=user_team.m1 AND/OR 
    monsterdata.id=user_team.m2 AND/OR ...

There you go!

Upvotes: 8

Related Questions