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.

pmodiano1Asked:
Who is Participating?
 
reb73Commented:
Something like this should do -
SELECT *
FROM
	(SELECT EmployeeId, 'False' as Dependent, FirstName, LastName, BirthDay
	 FROM Employee
	
	 UNION 
	
	 SELECT EmployeeId, 'True' as Dependent, FirstName, LastName, Birthday
	 FROM Dependents
	) AS Tmp
ORDER BY EmployeeId, Dependent

Open in new window

0
 
pmodiano1Author Commented:
Well done! Thanks so much!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.