Link to home
Start Free TrialLog in
Avatar of pmodiano1
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.

ASKER CERTIFIED SOLUTION
Avatar of reb73
reb73
Flag of Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of pmodiano1
pmodiano1

ASKER

Well done! Thanks so much!