CODEWITHSUNDEEP
mysql
Ief Falot
Ief Falot

Reputation: 13

In need of an SQL statement

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

Upvotes: 1

Views: 26

Answers (1)

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28854

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

Upvotes: 1

Related Questions