SQLTeam.com | Weblogs | Forums

Query within a query


#1

No good at SQL, but need this done. Need to write a single query that will pull results as on bottom table from table above it.


#2

What have you tried?
One way would be to left join the table to itself on ReportingTo_ID and ID.


#3

Easy to make it in two queries, but need all in one.


#4

Anyone can help with this, please?


#5

Please provide the table definitions, and what you have tried. My suggestion of a join should work.


#6

Table = "users"
ID (PK, int, not null)
User_name (nvarchar(100), null)
ReportingTo_ID (FK, int, null)

I was playing up with this in access db and it has slightly different names, but should give general idea.

Query1

SELECT [Field5]+', '+[Field4] AS Users
,CInt(IIf([Field37]='Null',0,[Field37])) AS exp
FROM users;

Query2

SELECT Query1.Users AS [User]
,[Field5]+', '+[Field4] AS Reports_to
FROM Query1 INNER JOIN Sheet1 ON Query1.exp = Sheet1.Field1
ORDER BY Query1.Users;


#7

How about something like:

SELECT U1.User_name, U2.User_name 
FROM Users U1 
LEFT JOIN Users U2 ON U1.ReportingTo_ID = U2.ID; 

#8

U1, U2 - individual queries?

I wanted all running in a single query.


#9

There is only one query here.
U1 and U2 are alias values which represent in this case the same table but two instances of that table since that data needed is in the table you need to reference that table twice.


#10

I will give it a try and let know if it worked.


#11

djj55, that was the key alias values. Didn't know the syntax. So, it worked, thanks very much for helping.