CODEWITHSUNDEEP
mysqlsqljoinjdbc
Madmenyo
Madmenyo

Reputation: 8584

Join multiple records on single record

I have this table (lineup):

ownerId (pk)
slot1 (fk heroId)
slot2 (fk heroId)
slot3 (fk heroId)
slot4 (fk heroId)
slot5 (fk heroId)

Now I need to retrieve the records from table (heroes) corresponding to the slots.

heroId (pk)
ownerId (fk)
templateId (fk)
...

And join this with the table (herotemplates).

templateId (pk)
...

So I came up with the following query:

String query = "SELECT * FROM lineup " +
"JOIN heroes AS slot1hero ON slot1hero.heroId = lineup.slot1 " +
"JOIN herotemplates AS slot1template ON slot1hero.templateId = slot1template.templateId " +

"JOIN heroes AS slot2hero ON slot2hero.heroId = lineup.slot2 " +
"JOIN herotemplates AS slot2template ON slot2hero.templateId = slot2template.templateId " +

"JOIN heroes AS slot3hero ON slot3hero.heroId = lineup.slot3 " +
"JOIN herotemplates AS slot3template ON slot3hero.templateId = slot3template.templateId " +

"JOIN heroes AS slot4hero ON slot4hero.heroId = lineup.slot4 " +
"JOIN herotemplates AS slot4template ON slot4hero.templateId = slot4template.templateId " +

"JOIN heroes AS slot5hero ON slot5hero.heroId = lineup.slot5 " +
"JOIN herotemplates AS slot5template ON slot5hero.templateId = slot5template.templateId " +

"WHERE lineup.ownerId = " + ownerId;

However, this returns an empty result. I highly suspect it's the query since I am not very experienced with complicated joins. Any idea what could be wrong with it?

I could fix this by normalizing lineup but I'm never going to use more ore less then 5 slots. I can also split into multiple queries but I'd rather query the database a single time and close the connection asap.

Upvotes: 0

Views: 44

Answers (1)

Morpheus
Morpheus

Reputation: 1634

This is difficult to answer without knowing what data is in the table. You would need a match for each slot since you are using JOIN. I think you may need to be using LEFT JOIN.

Upvotes: 1

Related Questions