Retain Benefits of Non-Normalized Table

[perlmunger]

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 45

I now have something like these two tables:

Table: entities

ID NAME
1 Entity 1
2 Entity 3

Table: entity_items

ENTITY_ID RELATED_ITEM_ID
1 1
1 24
1 67
2 14
2 45

I 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

[Mark J Miller]

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[^]

[perlmunger]

Thank you. That was helpful. -Matt

------------------------------------------ The 3 great virtues of a programmer: Laziness, Impatience, and Hubris. --Larry Wall