[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

SQL query - table joined to itself

Claire

3/30/2007 3:15:00 PM

database noob question.

My User table refers to itself on one field.
I have 3 types of user, and their UserType is stored in one column.
The 2nd tier type of user "belongs" to a 1st tier user. 0st tier users are
admins.
So I have the following fields

Users Table
=======
Int64 RecID; // ID of User
Int64 fk_Users_Parent_RecID; // ID of parent within users table
string FirstName;
string Surname;
byte UserType; //0 = admin, 1 = supervisor, 2 = pleb

fk_Users_Parent_RecID is null for tier0 and tier1 users. For tier2 users
this contains the value of a tier1 user.

I need a query that selects firstname and surname from the users table where
UserType = 2 Plus I want the First and Surname of their supervisor.
How do I do that please? I got as far as "select FirstName, Surname from
users" but got stuck on the supervisor part as it's referring to the same
table.

thanks :)
3 Answers

Tom Cooper

3/30/2007 3:41:00 PM

0

Just join the table to itself and give each copy of the table an alias

Select u.FirstName, u.SurName, s.FirstName As SupervisorFirstName, s.SurName
As SupervisorSurName
From Users As u
Left Join Users As s On u..fl_Users_Parent_RecID = s.RecID
Where UserType = 2;

Tom

"Claire" <claire@nospam.com> wrote in message
news:%23sq%23t4tcHHA.4004@TK2MSFTNGP06.phx.gbl...
> database noob question.
>
> My User table refers to itself on one field.
> I have 3 types of user, and their UserType is stored in one column.
> The 2nd tier type of user "belongs" to a 1st tier user. 0st tier users are
> admins.
> So I have the following fields
>
> Users Table
> =======
> Int64 RecID; // ID of User
> Int64 fk_Users_Parent_RecID; // ID of parent within users table
> string FirstName;
> string Surname;
> byte UserType; //0 = admin, 1 = supervisor, 2 = pleb
>
> fk_Users_Parent_RecID is null for tier0 and tier1 users. For tier2 users
> this contains the value of a tier1 user.
>
> I need a query that selects firstname and surname from the users table
> where UserType = 2 Plus I want the First and Surname of their supervisor.
> How do I do that please? I got as far as "select FirstName, Surname from
> users" but got stuck on the supervisor part as it's referring to the same
> table.
>
> thanks :)
>
>


Immy

3/30/2007 3:45:00 PM

0

Alias the tables and perform a self join

"Claire" <claire@nospam.com> wrote in message
news:%23sq%23t4tcHHA.4004@TK2MSFTNGP06.phx.gbl...
> database noob question.
>
> My User table refers to itself on one field.
> I have 3 types of user, and their UserType is stored in one column.
> The 2nd tier type of user "belongs" to a 1st tier user. 0st tier users are
> admins.
> So I have the following fields
>
> Users Table
> =======
> Int64 RecID; // ID of User
> Int64 fk_Users_Parent_RecID; // ID of parent within users table
> string FirstName;
> string Surname;
> byte UserType; //0 = admin, 1 = supervisor, 2 = pleb
>
> fk_Users_Parent_RecID is null for tier0 and tier1 users. For tier2 users
> this contains the value of a tier1 user.
>
> I need a query that selects firstname and surname from the users table
> where UserType = 2 Plus I want the First and Surname of their supervisor.
> How do I do that please? I got as far as "select FirstName, Surname from
> users" but got stuck on the supervisor part as it's referring to the same
> table.
>
> thanks :)
>
>


Tom Moreau

3/30/2007 4:06:00 PM

0

Try;

select
e.Surname
, e.FirstName
, m.Surname as ManagerSurname
, m.FirstName as ManagerFirstName
from
users e
join
users m on m.RecID = e.fk_Users_Parent_RecID

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"Immy" <therealasianbabe@hotmail.com> wrote in message
news:%23HPI1JucHHA.3960@TK2MSFTNGP04.phx.gbl...
Alias the tables and perform a self join

"Claire" <claire@nospam.com> wrote in message
news:%23sq%23t4tcHHA.4004@TK2MSFTNGP06.phx.gbl...
> database noob question.
>
> My User table refers to itself on one field.
> I have 3 types of user, and their UserType is stored in one column.
> The 2nd tier type of user "belongs" to a 1st tier user. 0st tier users are
> admins.
> So I have the following fields
>
> Users Table
> =======
> Int64 RecID; // ID of User
> Int64 fk_Users_Parent_RecID; // ID of parent within users table
> string FirstName;
> string Surname;
> byte UserType; //0 = admin, 1 = supervisor, 2 = pleb
>
> fk_Users_Parent_RecID is null for tier0 and tier1 users. For tier2 users
> this contains the value of a tier1 user.
>
> I need a query that selects firstname and surname from the users table
> where UserType = 2 Plus I want the First and Surname of their supervisor.
> How do I do that please? I got as far as "select FirstName, Surname from
> users" but got stuck on the supervisor part as it's referring to the same
> table.
>
> thanks :)
>
>