How to Delete in MS SQL WHERE IN (SELECT Multiple Columns)

Question

i have a table lets call it "Cluster" which is related an second table Element

Cluster:

Id Group Ele1 Ele2 Ele3
1   1     1    2     3
2   1     4   NULL   9
3   2     5    8     7

Element

Id Name
1  'A'
2  'b'
3  'c'
4  'd'
5  'z'
6  'j'
7  't'
8  'r'
9  'e'

now i have to delete an Cluster

DELETE FROM Cluster
WHERE   Cluster.Group= 1

but before i delete the rows from my Cluster i need to delete all related rows from Element

DELETE FROM Element
WHERE Id IN (SELECT Ele1 Ele2 Ele3 
             FROM Cluster 
             WHERE Cluster.Group= 1)

but this query doesn't work so what do i miss?

Answer 1

Try this:

DELETE FROM Element
WHERE Id IN (SELECT Ele1
             FROM Cluster 
             WHERE Cluster.[Group] = 1
                   And Ele1 Is Not NULL

             Union 

             SELECT Ele2
             FROM Cluster 
             WHERE Cluster.[Group] = 1
                   And Ele2 Is Not NULL

             Union 

             SELECT Ele3 
             FROM Cluster 
             WHERE Cluster.[Group] = 1
                   And Ele3 Is Not NULL
)