Complex Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Complex Query

Author

Topic

JimAmigo
Posting Yak Master

119 Posts

Posted - 2007-04-11 : 16:16:49

Due to some legacy database issues I need to find a creative way to format one query to join tow tables (I assume three times) to retrieve the information I need.

Two tables, tbl_Data and tbl_Staff_rel_Staff

tbl_Data is the main data table and includes both staff and managers records with a primary key named profileid.

tbl_Staff_rel_Staff is the table where the manager to profile relationshiop is stored. This table is "stamed" when records are created to link which staff belongs to which managers.

Example of Records in tbl_Staff_rel_Staff

Record 1.

ManagerProfileID = 36985
StaffProfileID = 25698

Record 2.

ManagerProfileID = 36985
StaffProfileID = 26359

So in this case manager 36985 really has just 2 staff member, 25698 and 26359. The real problem is that I need to report the manager as a memeber of the staff in the report as well. So really the staff includes 3 staff memeber both staff and manager.

Both the ManagerProfileID and StaffProfileID are the profileIDs of the records in tbl_Data.

Here is an example of what I need to show.

Example of query fields...

Mgr_FirstName Mgr_LastName Mgr_ProfileID, Stf_FirstName, Stf_LastName, StaffProfileID, Salary

So for the query would return form the records above...

Fred Smit 36985 Fred Smit 36985 52,000
Fred Smit 36985 Judy Read 25698 25,000
Fred Smit 36985 Jeff Cool 26359 18,000

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-11 : 16:20:35

[code]SELECT mgr.FirstName AS Mgr_FirstName,
mgr.LastName AS Mgr_LastName,
mgr.ProfileID AS Mgr_ProfileID,
stf.FirstName AS Stf_FirstName,
stf.LastName AS Stf_LastName,
stf.ProfileID AS StaffProfileID,
stf.Salary
FROM (
SELECT ManagerProfileID,
StaffProfileID
FROM tbl_Staff_rel_Staff

UNION ALL

SELECT ManagerProfileID,
ManagerProfileID
FROM tbl_Staff_rel_Staff
) AS d
INNER JOIN @tbl_Data AS mgr ON mgr.ProfileID = d.ManagerProfileID
INNER JOIN @tbl_Data AS stf ON stf.ProfileID = d.StaffProfileID[/code]
Peter Larsson
Helsingborg, Sweden

JimAmigo
Posting Yak Master

119 Posts

Posted - 2007-04-11 : 16:36:39

I got error.. Must declare the variable @tbl_Data..

JimAmigo
Posting Yak Master

119 Posts

Posted - 2007-04-11 : 16:47:20

Never mind.. I removed the @ and it seems to work. I forgot I had to include some other fields as well. I appreicate all your help.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-04-11 : 16:47:43

quote:

Originally posted by Peso

SELECT		mgr.FirstName AS Mgr_FirstName,
		mgr.LastName AS Mgr_LastName,
		mgr.ProfileID AS Mgr_ProfileID,
		stf.FirstName AS Stf_FirstName,
		stf.LastName AS Stf_LastName,
		stf.ProfileID AS StaffProfileID,
		stf.Salary
FROM		(
			SELECT	ManagerProfileID,
				StaffProfileID
			FROM	tbl_Staff_rel_Staff

			UNION ALL

			SELECT	ManagerProfileID,
				ManagerProfileID
			FROM	tbl_Staff_rel_Staff
		) AS d
INNER JOIN	@tbl_Data AS mgr ON mgr.ProfileID = d.ManagerProfileID
INNER JOIN	@tbl_Data AS stf ON stf.ProfileID = d.StaffProfileID

Peter Larsson
Helsingborg, Sweden

EDIT: Doh, too late.

JimAmigo
Posting Yak Master

119 Posts

Posted - 2007-04-11 : 17:02:44

Ok, an error in the data returned. There manager is repeated once for each staff member returned. And example: If a manager has 10 staff members then the manager is showing up 10 times as a staff member as well.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-11 : 17:40:08

Really?
Let's see the query you have right now. Please post it here.

Peter Larsson
Helsingborg, Sweden

JimAmigo
Posting Yak Master

119 Posts

Posted - 2007-04-12 : 09:27:50

quote:
Originally posted by Peso

Really?
Let's see the query you have right now. Please post it here.

Peter Larsson
Helsingborg, Sweden

SELECT mgr.FirstName AS Mgr_FirstName,
mgr.LastName AS Mgr_LastName,
mgr.ProfileID AS Mgr_ProfileID,
stf.FirstName AS Stf_FirstName,
stf.LastName AS Stf_LastName,
stf.ProfileID AS StaffProfileID,
stf.Salary
FROM (
SELECT ManagerProfileID,
StaffProfileID
FROM tbl_Staff_rel_Staff

UNION ALL

SELECT ManagerProfileID,
ManagerProfileID
FROM tbl_Staff_rel_Staff

) AS d
INNER JOIN tbl_Data AS mgr ON mgr.ProfileID = d.ManagerProfileID
INNER JOIN tbl_Data AS stf ON stf.ProfileID = d.StaffProfileID
WHERE d.ManagerProfileID = '34129'

Maybe I have the WHERE in the wrong place?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-12 : 10:24:46

First, run this and post the result here

select ManagerProfileID, StaffProfileID from (
SELECT ManagerProfileID,
StaffProfileID
FROM tbl_Staff_rel_Staff where ManagerProfileID = '34129'

UNION ALL

SELECT ManagerProfileID,
ManagerProfileID
FROM tbl_Staff_rel_Staff where ManagerProfileID = '34129'
) order by ManagerProfileID, StaffProfileID

And then run this and post result here

select ProfileID from tbl_Data where ProfileID = '34129'

Peter Larsson
Helsingborg, Sweden

JimAmigo
Posting Yak Master

119 Posts

Posted - 2007-04-12 : 10:43:28

quote:
Originally posted by Peso

First, run this and post the result here

select ManagerProfileID, StaffProfileID from (
SELECT ManagerProfileID,
StaffProfileID
FROM tbl_Staff_rel_Staff where ManagerProfileID = '34129'

UNION ALL

SELECT ManagerProfileID,
ManagerProfileID
FROM tbl_Staff_rel_Staff where ManagerProfileID = '34129'
) order by ManagerProfileID, StaffProfileID

And then run this and post result here

select ProfileID from tbl_Data where ProfileID = '34129'

Peter Larsson
Helsingborg, Sweden

For this...

select ManagerProfileID, StaffProfileID from (
SELECT ManagerProfileID,
StaffProfileID
FROM tbl_Staff_rel_Staff where ManagerProfileID = '34129'

UNION ALL

SELECT ManagerProfileID,
ManagerProfileID
FROM tbl_Staff_rel_Staff where ManagerProfileID = '34129'
)order by ManagerProfileID, StaffProfileID

I got an error

Server: Msg 156, Level 15, State 1, Line 11
Incorrect syntax near the keyword 'order'.

JimAmigo
Posting Yak Master

119 Posts

Posted - 2007-04-12 : 10:45:10

This...Select ProfileID from tbl_Data where ProfileID = '34129'

Returned, 1 record...

ProfileID
34129

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-12 : 10:45:32

[code]
select ManagerProfileID, StaffProfileID from (
SELECT ManagerProfileID,
StaffProfileID
FROM tbl_Staff_rel_Staff where ManagerProfileID = '34129'

UNION ALL

SELECT ManagerProfileID,
ManagerProfileID
FROM tbl_Staff_rel_Staff where ManagerProfileID = '34129'
) AS a order by ManagerProfileID, StaffProfileID
[/code]

JimAmigo
Posting Yak Master

119 Posts

Posted - 2007-04-12 : 10:53:15

This....

select ManagerProfileID, StaffProfileID from (
SELECT ManagerProfileID,
StaffProfileID
FROM tbl_Staff_rel_Staff where ManagerProfileID = '34129'

UNION ALL

SELECT ManagerProfileID,
ManagerProfileID
FROM tbl_Staff_rel_Staff where ManagerProfileID = '34129'
) AS a order by ManagerProfileID, StaffProfileID

Returned NO records, just two field names ManagerProfielID StaffProfileID

JimAmigo
Posting Yak Master

119 Posts

Posted - 2007-04-13 : 09:41:38

quote:
Originally posted by Peso

First, run this and post the result here

select ManagerProfileID, StaffProfileID from (
SELECT ManagerProfileID,
StaffProfileID
FROM tbl_Staff_rel_Staff where ManagerProfileID = '34129'

UNION ALL

SELECT ManagerProfileID,
ManagerProfileID
FROM tbl_Staff_rel_Staff where ManagerProfileID = '34129'
) order by ManagerProfileID, StaffProfileID

And then run this and post result here

select ProfileID from tbl_Data where ProfileID = '34129'

Peter Larsson
Helsingborg, Sweden

I did as you suggested.. any thoughts or recommendations?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-13 : 09:43:55

SELECT * FROM tbl_Staff_rel_Staff where ManagerProfileID = '34129'

Peter Larsson
Helsingborg, Sweden

JimAmigo
Posting Yak Master

119 Posts

Posted - 2007-04-13 : 09:55:01

quote:
Originally posted by JimAmigo

This...Select ProfileID from tbl_Data where ProfileID = '34129'

Returned, 1 record...

ProfileID
34129

This returned one record.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-13 : 09:57:35

And still you tell me that the JOIN of 1 record to 1 record returns 10 records more?

SELECT		mgr.FirstName AS Mgr_FirstName,
		mgr.LastName AS Mgr_LastName,
		mgr.ProfileID AS Mgr_ProfileID,
		stf.FirstName AS Stf_FirstName,
		stf.LastName AS Stf_LastName,
		stf.ProfileID AS StaffProfileID,
		stf.Salary
FROM		(
			SELECT	ManagerProfileID,
				StaffProfileID
			FROM	tbl_Staff_rel_Staff

			UNION ALL

			SELECT	ManagerProfileID,
				ManagerProfileID
			FROM	tbl_Staff_rel_Staff
		) AS d
INNER JOIN	tbl_Data AS mgr ON mgr.ProfileID = d.ManagerProfileID
INNER JOIN	tbl_Data AS stf ON stf.ProfileID = d.StaffProfileID
WHERE		d.ManagerProfileID = '36985'

Peter Larsson
Helsingborg, Sweden

JimAmigo
Posting Yak Master

119 Posts

Posted - 2007-04-13 : 10:04:30

quote:
Originally posted by Peso

SELECT * FROM tbl_Staff_rel_Staff where ManagerProfileID = '34129'

Peter Larsson
Helsingborg, Sweden

For the record ManagerProfileID = '34129' This query returns 10 records, which is correct for the staff that this manager has.

In this case our desired result from our initial query should have 11 records, The 10 staff and 1 manager. But it returned a manager listing for ever staff member, resulting in the 20 records.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-13 : 10:08:59

quote:
Originally posted by JimAmigo

But it returned a manager listing for ever staff member, resulting in the 20 records.

Are you sure you have valid data?

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-13 : 10:09:27

quote:
Originally posted by JimAmigo

In this case our desired result from our initial query should have 11 records.

The red part will give you 11 records!

Use the query posted 04/13/2007 : 09:57:35 !!!
It will give you the rigth data.

If you think it still doesn't, PLEASE POST SAMPLE DATA AND EXPECTED OUTPUT HERE.

Peter Larsson
Helsingborg, Sweden

JimAmigo
Posting Yak Master

119 Posts

Posted - 2007-04-13 : 10:09:31

quote:

Originally posted by Peso

And still you tell me that the JOIN of 1 record to 1 record returns 10 records more?

SELECT		mgr.FirstName AS Mgr_FirstName,
		mgr.LastName AS Mgr_LastName,
		mgr.ProfileID AS Mgr_ProfileID,
		stf.FirstName AS Stf_FirstName,
		stf.LastName AS Stf_LastName,
		stf.ProfileID AS StaffProfileID,
		stf.Salary
FROM		(
			SELECT	ManagerProfileID,
				StaffProfileID
			FROM	tbl_Staff_rel_Staff

			UNION ALL

			SELECT	ManagerProfileID,
				ManagerProfileID
			FROM	tbl_Staff_rel_Staff
		) AS d
INNER JOIN	tbl_Data AS mgr ON mgr.ProfileID = d.ManagerProfileID
INNER JOIN	tbl_Data AS stf ON stf.ProfileID = d.StaffProfileID
WHERE		d.ManagerProfileID = '36985'

Peter Larsson
Helsingborg, Sweden

Peter my apologies, I may have caused you some confusion.

ManagerProfileID, 36985 was a fictitious example I initially used to help simplify the my example. ManagerProfileID, 34129 is a real ManagerProfileID that has a staff of 10.

So it seems the query is returning a Manager record for each staff record.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources