How to write a single SQL query that lists master and detail records in hierarchy form

Posted on 2009-04-30
Medium Priority
Last Modified: 2012-05-06

I am trying to create a query that will generate a master/detail report in MS SQL 2005.  Here are my two tables:

EmployeeID int PK
Firstname varchar
Lastname varchar
Birthday smalldatetime

DependentID int PK
EmployeeID int  (links to Employees.EmployeeID)
Firstname varchar
lastname varchar

Let's assume that the following records are in there:

EmployeeID / Firstname / lastname /birthday
1                 / John         / Doe         / 1/1/1980
2                 / Porky        / Pig          / 1/1/1982

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.



Question by:pmodiano1
LVL 25

Accepted Solution

reb73 earned 2000 total points
ID: 24273203
Something like this should do -
	(SELECT EmployeeId, 'False' as Dependent, FirstName, LastName, BirthDay
	 FROM Employee
	 SELECT EmployeeId, 'True' as Dependent, FirstName, LastName, Birthday
	 FROM Dependents
	) AS Tmp
ORDER BY EmployeeId, Dependent

Open in new window


Author Closing Comment

ID: 31576592
Well done! Thanks so much!

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question