CODEWITHSUNDEEP
sqlabap
Florian
Florian

Reputation: 5994

select rows with zero child elements (join)

I've got 2 tables. There is a constraint between these 2 tables.

Table1 : Table2 = 1 : n

I would like to select the keys of the Table1 elements which GOT no entry in Table2.

Example: 

|    Table 1         |
|Key |Col1 |Col2 |...|
|0001|.... |.... |...|
|0002|.... |.... |...|
|0003|.... |.... |...|
|0004|.... |.... |...|

|    Table 2         |
|Tab1|Col1 |Col2 |...|
|0001|.... |.... |...|
|0001|.... |.... |...|
|0003|.... |.... |...|
|0002|.... |.... |...|

Desired output:
0004

The output is the only entry of table1 which got no child entry in table2.

I've tried many different options like:

SELECT 
    Key
    FROM Table1
    WHERE 
        ( SELECT COUNT(*) FROM Table2 
            WHERE Table1~Key = Table2~Tab1 
            GROUP BY Table2~Tab1 )

SELECT COUNT(Table2)
    Key
    FROM Table1
    INNER JOIN Table2 
        ON Table1~Key = Table2~Tab1.

...

But none of them work. Does anyone got a working query for ABAP?

Upvotes: 0

Views: 82

Answers (4)

inetphantom
inetphantom

Reputation: 2595

Solution without subselect: 

SELECT t1~Key FROM Table1 as t1
LEFT JOIN Table2 as t2
WHERE t2~Key is null

Upvotes: 0

Emipro Technologies Pvt. Ltd.
Emipro Technologies Pvt. Ltd.

Reputation: 14746

Try following,

SELECT Key FROM Table1
WHERE key not in (SELECT distinct tab1 FROM Table2)

Upvotes: 1

Mukesh Kalgude
Mukesh Kalgude

Reputation: 4844

Try this query

select key from table1 where not EXISTS (SELECT *
              FROM table2
              WHERE table1.key=table2.tab1);

Upvotes: 2

Randy
Randy

Reputation: 16677

select * from table1 where key not in (select tab1 from table2)

Upvotes: 2

Related Questions