CODEWITHSUNDEEP
sqlsql-server
Eitan
Eitan

Reputation: 1504

Single SQL query that can return a row with a reference to another row in the same table?

I have a table called Resources that can reference another record in the same table. I was wondering if there's any way to run a query on both the "parent" record and the "child" record with the same query. I would preferably like to do this in one query without a UNION. There aren't any grandchildren and there is only one child, 1:1. 

SELECT Id, Name, OrgId, ClassId 
FROM Resources 
WHERE OrgId = 1 AND ClassId = 2 
OR (if ResourceID is not null, then also return the row where Id = ResourceId)

I know I can JOIN the child record but then I would have double the number of columns in the resultset i.e. parent.Id, child.id, parent.Name, child.Name.

Can anyone shed any light?

Edit:

Sample data:

|   Id   |   Name   |   OrgId   |   ClassId   |  ResourceId |
|--------|----------|-----------|-------------|-------------|
|   1    |  File    |    1      |      2      |      2      |
|--------|----------|-----------|-------------|-------------|
|   2    | Picture  |    2      |      1      |      NULL   |
|--------|----------|-----------|-------------|-------------|

Is there a query that would run a specific search for example give me all the resources that orgId = 1 and ClassId = 2 and then check if there's a connected Resource in ResourceId and return that one as well.

Upvotes: 0

Views: 42

Answers (1)

forpas
forpas

Reputation: 164164

With EXISTS:

SELECT r.Id, r.Name, r.OrgId, r.ClassId 
FROM Resources r 
WHERE (r.OrgId = 1 AND r.ClassId = 2)
   OR EXISTS (
     SELECT 1 FROM Resources
     WHERE OrgId = 1 AND ClassId = 2 AND ResourceID = r.ID
)

Upvotes: 1

Related Questions