pmodiano1
asked on
How to write a single SQL query that lists master and detail records in hierarchy form
Hi,
I am trying to create a query that will generate a master/detail report in MS SQL 2005. Here are my two tables:
Employee
------------
EmployeeID int PK
Firstname varchar
Lastname varchar
Birthday smalldatetime
Dependents
--------------
DependentID int PK
EmployeeID int (links to Employees.EmployeeID)
Firstname varchar
lastname varchar
birthday
Let's assume that the following records are in there:
Employees
-------------
EmployeeID / Firstname / lastname /birthday
1 / John / Doe / 1/1/1980
2 / Porky / Pig / 1/1/1982
Dependents
--------------
DependentID / EmployeeID / Firstname / lastname /birthday
1 / 1 / Jane / Doe / 2/2/1980
2 / 1 / Baby / Doe / 2/2/2009
3 / 2 / Babe / Pig / 2/2/1990
What kind of query could I use that would generate these results?
EmployeeID / Dependent / Firstname / lastname / birthday
1 / False / John / Doe / 1/1/1980
1 / True / Jane / Doe / 2/2/1980
1 / True / Baby / Doe / 2/2/2009
2 / False / Porky / Pig / 1/1/1982
I would appreciate any guidance you can give me.
Thanks,
Paul.
I am trying to create a query that will generate a master/detail report in MS SQL 2005. Here are my two tables:
Employee
------------
EmployeeID int PK
Firstname varchar
Lastname varchar
Birthday smalldatetime
Dependents
--------------
DependentID int PK
EmployeeID int (links to Employees.EmployeeID)
Firstname varchar
lastname varchar
birthday
Let's assume that the following records are in there:
Employees
-------------
EmployeeID / Firstname / lastname /birthday
1 / John / Doe / 1/1/1980
2 / Porky / Pig / 1/1/1982
Dependents
--------------
DependentID / EmployeeID / Firstname / lastname /birthday
1 / 1 / Jane / Doe / 2/2/1980
2 / 1 / Baby / Doe / 2/2/2009
3 / 2 / Babe / Pig / 2/2/1990
What kind of query could I use that would generate these results?
EmployeeID / Dependent / Firstname / lastname / birthday
1 / False / John / Doe / 1/1/1980
1 / True / Jane / Doe / 2/2/1980
1 / True / Baby / Doe / 2/2/2009
2 / False / Porky / Pig / 1/1/1982
I would appreciate any guidance you can give me.
Thanks,
Paul.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER