SQL - Query same table field multiple times

h0stn0tf0und

I am just trying to get to grips with SQL and hope someone here can direct/correct me.
I have a table of users

Users
..uid (PK)
..ufirstname
…. etc

I then have a table the contains Positions

Positions
..pid (PK)
..employeeid
..supervisorid
..managerid
… etc

employeeid is the PK of a user in the Users table.

What I am wondering is can I query a field in the Users table several times in the one query and assign unique values.

Example
I query a user and I want to return

employee name – supervisor name – managers name


Thanks

Tom Dunne

Where are the supervisor's and manager's names stored?

h0stn0tf0und

In the users table.

Tom Dunne

h0stn0tf0und wrote: »

In the users table.

So, is there a direct mapping from supervisorid to userid and managerid to userid, for managers and supervisors?

Also, what database are you using? Not that it should really matter, I'm just nosey like that.

Kernel32

Selec employee.uid, employee.ufirstname, supervisor.ufirstname, manager.ufirstname from Users employee inner join Positions on employee.uid = Postions.employeeid left join Users supervisor on Positions.supervisorid = supervisor.uid left join Users manager on Positions.managerid = manager.uid

or something similar. The key is to join the table and use aliases for the tablename.

kayos

Ah the joy of Hierarchical data in a relational DB. To answer your question, from what I can make of it, yes you can.

SELECT
	U.Username,
	S.Username,
	M.username
FROM
	Users AS U
	JOIN Positions AS P ON (U.uid = P.employeeID)
	JOIN Users AS S ON (S.uid = P.SupervisorID)
	JOIN Users AS M ON (M.uid = P.ManagerID)
WHERE
	U.uid = 1

Or if you want to get fancy and are using SQL Server 2005 or above try a CTE, very simple example below.

Create Table #Users
(
uid INT identity(1,1) NOT NULL,
username nvarchar(100) NOT NULL,
ParentUID INT NULL
)
DECLARE @parentID INT

INSERT #USERS
(
Username,
ParentUID
)
SELECT
	'jbloggs',
	null

SELECT @parentID = SCOPE_Identity()
INSERT #USERS
(
Username,
ParentUID
)
SELECT
	'jsmith',
	@ParentID
UNION
SELECT
	'bsmyth',
	@ParentID

SELECT @ParentID = SCOPE_IDENTITY()
INSERT #USERS
(
Username,
ParentUID
)
SELECT
	'aperson',
	@ParentID
GO
WITH CTE_Demo(ManagerID, EmployeeID, [Level])
AS
(
	SELECT
		u.ParentUID AS ManagerID,
		u.UID AS EmployeeID,
		0 AS [Level]
	FROM 
		#Users AS U
	WHERE
		U.ParentUID IS NULL
	UNION ALL
	SELECT
		u.ParentUID AS ManagerID,
		u.UID AS EmployeeID,
		C.[Level] + 1 AS [Level]
	FROM 
		#Users AS U
		JOIN CTE_Demo AS C ON (U.ParentUID = C.EmployeeID)
)
SELECT * FROM CTE_Demo 

DROP TABLE #Users

h0stn0tf0und

Hi guys,

Thanks very much for all the help.

So, is there a direct mapping from supervisorid to userid and managerid to userid, for managers and supervisors?

Also, what database are you using? Not that it should really matter, I'm just nosey like that.

Yes to your first question.
I'm playing with Access 2003.

Kernel32 & Kayos, using an alias seems to be the solution to the problem, getting the syntax correct for Access is the new problem! I am going to tackle it now and maybe back with a few questions.

Thanks