Retain Benefits of Non-Normalized Table
-
I normalized a table in a database project I'm working on so that instead of having something like this:
Table: entities
ID NAME RELATED_ITEM_ID_1 RELATED_ITEM_ID_2 ..... RELATED_ITEM_ID_40
1 Entity 1 1 24 67
2 Entity 3 14 NULL 45I now have something like these two tables:
Table: entities
ID NAME
1 Entity 1
2 Entity 3Table: entity_items
ENTITY_ID RELATED_ITEM_ID
1 1
1 24
1 67
2 14
2 45I want to return a table that has a summary of the entities table returning the top three entity_items so that it will come back looking like the original (non-normalized) entities table. I am using a MySQL database, but any help with the basic concepts and SQL here would be appreciated. I'm sure this is a common problem people deal with, but I didn't even know the terminology to use to find an answer on the web. Thanks. -Matt
------------------------------------------ The 3 great virtues of a programmer: Laziness, Impatience, and Hubris. --Larry Wall
-
I normalized a table in a database project I'm working on so that instead of having something like this:
Table: entities
ID NAME RELATED_ITEM_ID_1 RELATED_ITEM_ID_2 ..... RELATED_ITEM_ID_40
1 Entity 1 1 24 67
2 Entity 3 14 NULL 45I now have something like these two tables:
Table: entities
ID NAME
1 Entity 1
2 Entity 3Table: entity_items
ENTITY_ID RELATED_ITEM_ID
1 1
1 24
1 67
2 14
2 45I want to return a table that has a summary of the entities table returning the top three entity_items so that it will come back looking like the original (non-normalized) entities table. I am using a MySQL database, but any help with the basic concepts and SQL here would be appreciated. I'm sure this is a common problem people deal with, but I didn't even know the terminology to use to find an answer on the web. Thanks. -Matt
------------------------------------------ The 3 great virtues of a programmer: Laziness, Impatience, and Hubris. --Larry Wall
I've never used mySQL so I don't have a solution, but you are probably want to search for 'Pivot Table' or 'Crosstab Query'. Here's one link I pulled up really quick searching for 'mySql Crosstab Query': http://rpbouman.blogspot.com/2005/10/creating-crosstabs-in-mysql.html[^]
-
I've never used mySQL so I don't have a solution, but you are probably want to search for 'Pivot Table' or 'Crosstab Query'. Here's one link I pulled up really quick searching for 'mySql Crosstab Query': http://rpbouman.blogspot.com/2005/10/creating-crosstabs-in-mysql.html[^]
Thank you. That was helpful. -Matt
------------------------------------------ The 3 great virtues of a programmer: Laziness, Impatience, and Hubris. --Larry Wall