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.
What have you tried?
One way would be to left join the table to itself on ReportingTo_ID and ID.
Easy to make it in two queries, but need all in one.
Anyone can help with this, please?
Please provide the table definitions, and what you have tried. My suggestion of a join should work.
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;
How about something like:
SELECT U1.User_name, U2.User_name
FROM Users U1
LEFT JOIN Users U2 ON U1.ReportingTo_ID = U2.ID;
1 Like
U1, U2 - individual queries?
I wanted all running in a single query.
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.
I will give it a try and let know if it worked.
djj55, that was the key alias values. Didn't know the syntax. So, it worked, thanks very much for helping.