not getting expected result while joining

Question

i want to display #acc that has no child here result must be B,C,D,E but getting B,C,D only

create table #acc (mainid int,name nvarchar(20),subid int)

    insert into #acc values(1,'A',0)
    insert into #acc values(2,'B',1)
    insert into #acc values(3,'C',1)
    insert into #acc values(4,'D',1)
    insert into #acc values(5,'E',0)

    select  A.name from #acc
    A inner join #acc B
    on 
    A.subid   = B.mainid    

    drop table #acc

What's the difference between A and E? Why do you expect that you'd get E but not A? — JJJ, Nov 12 '11 at 10:35

score 1 · Answer 1 · edited May 23 '17 at 12:11

First of all, I think you should rename the subid column to superid or parentid or something like that, because it is B, C & D that are sub-items of A, not the other way round. Maybe the inconsistent naming is exactly the reason why the results of your query seem incomprehensible to you or why you find it difficult to construct a query that returns the correct results.

Your query is essentially returning items that are some other items' children. They themselves may or may not have their own children. For example, if B, C or D had children, your query would return those children in addition to B, C and D. That does not seem exactly what you are after.

What you need here is not an inner join but an anti-join. It is when results are returned based on the fact that something has not matched. Anti-joins can be implemented in different ways:

Using LEFT JOIN + IS NULL check:
```
SELECT A.*
FROM #acc A
  LEFT JOIN #acc B ON A.mainid = B.subid
WHERE B.mainid IS NULL
```
Here we are joining the table to itself and returning the left side of the join where the right side have had no matches (i.e. returning rows with mainid values which are never found in the subid column).
Using NOT EXISTS:
```
SELECT *
FROM #acc A
WHERE NOT EXISTS (
  SELECT *
  FROM #acc B
  WHERE A.mainid = B.subid
)
```
This query can be interpreted thus: return every row from #acc when there doesn't exist a match between that row's mainid and any other row's subid.
Using NOT IN:
```
SELECT *
FROM #acc
WHERE mainid NOT IN (
  SELECT subid
  FROM #acc
)
```
This seems to me most straightforward (though not necessarily most efficient): return rows where mainid is not in the list of all existing subid values. If you used NULLs instead of 0 as root items' subid values, you'd also have to amend the last query by adding this filter to the subquery:
```
…
WHERE subid IS NOT NULL
```
Otherwise it would work incorrectly.

You might also want to read this thread:

What's the difference between NOT EXISTS vs. NOT IN vs. LEFT JOIN WHERE IS NULL?

score 0 · Accepted Answer · answered Nov 12 '11 at 10:40

0

This will do it

      select * from
        (select  A.mainid , A.name  from #acc
A left join #acc B
on A.subid   = B.mainid  ) as m where  m.mainid not in (select subid from #acc)

answered Nov 12 '11 at 10:40

Nighil

4,099
7
30
56

not getting expected result while joining

2 Answers2

Linked