I don’t know if I am the only one who has ever asked this. But I don’t seem to be able to find any information about it. Here is what I want to do.
Create table Company Id int auto_increment primary key, Name varchar(20); Create table Employee Id int auto_increment primary key, CompanyId int FirstName varchar(20), LastName varchar(20), Insert into Company (Name) values(“Honeywell”); ## ID becomes 1 Insert into Company (Name) values(“Microslop”); ## ID becomes 2 Insert into Employee (CompanyId, FirstName, LastName) (1, “Dave”, “Smith”) Insert into Employee (CompanyId, FirstName, LastName) (1, “Henry”, “white”) Insert into Employee (CompanyId, FirstName, LastName) (1, “Budd”, “Jenson”) Insert into Employee (CompanyId, FirstName, LastName) (2, “Mike”, “Strader”) Insert into Employee (CompanyId, FirstName, LastName) (2, “Dan”, “Warner”) Insert into Employee (CompanyId, FirstName, LastName) (2, “Jim”, “Jones”) Then I want to select data and have the results sets look as follows. Select Company.Name, Employee.FirstName, Employee.LastName from Company, Employee Where Company.Id = Employee.CompanyID I want this to return a result set that looks like this. Honeywell | Dave | Smith Henry | White Budd | Jenson Microslop Mike | Strader Dan | Warner Jim | Jones I realize that this data structured could be represented differently in different languages or utilities but understand it is a very general question. Basically when the code is grabbing the data from the data files instead of putting a complete record for each match it could create data in a hierarchal manner. I realize that I could write code to do this but in trying to determine a very generic way of doing it seems difficult and inefficient. My thinking is (I could be totally wrong please explain if I am) that it would be very efficient for the database server to do this. One it seems the natural process of the IO that is occurring would lend itself to this very well. Two it would significantly reduce the amount of data the database would need to return. Three emulating the flattened result set using a normalized result set would be much more efficient. Another way of asking the question would be: A result from a query seems to be very flattened. Is there with mysql (or any database for that matter) a way to view a result set in a normalized fashion.