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.
