1

I am trying to show a property value from another table, linked by an id. Consider having following two tables:

Entity( idEntity, idResource1, idResource2, idResource3, ...)

Resource( idResource, nameResource, shortNameResource, ...)

If I query

SELECT * FROM Entity WHERE idEntity = 5;

How can I get a response where instead of the id's, it gets me the nameResource or shortNameResource property?

I was trying using a join, but then I seem to only be able to join once one idResource1...

Thanks

Community
  • 1
  • 1
Ief Falot
  • 13
  • 2
  • Please show your attempt at your join by [editing](https://stackoverflow.com/posts/53035764/edit) your question. – Johan Oct 28 '18 at 20:30
  • I was incorrectly joining multiple times the same resource table as opposed to 'as r1', 'as r2', ... Thanks for your remark, I'll keep it in mind for my next questions. – Ief Falot Oct 28 '18 at 21:08

1 Answers1

1

It is a symptom of Bad table design when you create multiple number of columns to represent a similar field. Ideally, you should normalize your table structure. You could have a mapping table storing multiple resource_id values for an Entity, in different rows.

Eg: entity_to_resource table having fields (idEntity, idResource, orderResource). orderResource will be an integer value such as 1,2,3 and so on. It would define whether it is Resource1, Resource2, Resource3 and so on..

Now, in this case, you will need to use multiple Joins with the Resource table:

SELECT e.idEntity, 
       r1.nameResource AS nameResource1, 
       r1.shortNameResource AS shortNameResource1,
       r2.nameResource AS nameResource2, 
       r2.shortNameResource AS shortNameResource2,
       r3.nameResource AS nameResource3, 
       r3.shortNameResource AS shortNameResource3 
FROM Entity AS e 
JOIN Resource AS r1 ON r1.idResource = e.idResource1 
JOIN Resource AS r2 ON r2.idResource = e.idResource2 
JOIN Resource AS r3 ON r3.idResource = e.idResource3 
WHERE e.idEntity = 5
Madhur Bhaiya
  • 28,155
  • 10
  • 49
  • 57
  • Thanks! I guess you are right about the extra table. On my specific requirement I just need exactly 3 resources, so I guess that's why I 'forgot' about using an extra mapping table. Thanks for the answer though, it's working now! – Ief Falot Oct 28 '18 at 20:53