sql - get parentID with or without child in self refrenced table

Question

Here is my table's (events) content. eventID is "primary key" and parentID is "foreign key" with references to events(eventsID)

self referenced table :

    eventID eventName parentID appFK
    1       evt1      null     2
    2       evt2      1        1
    3       evt3      1        1
    4       evt4      null     3
    5       evt5      8        3
    6       evt6      null     4
    7       evt7      null     1
    8       evt8      null     1

and another table content (applications) like this :

    
    appID appName 
    1     app1      
    2     app2      
    3     app3      
    4     app4

I'd like to fetch all eventIDs which are parents or not with a given appID. If a child has the given appID, i'd like to get his parentID and not himself. So the result is going to be like this with appID = 1 :

    eventID eventName ParentID appFK
    1       evt1      null     2     // parent event who has children with appID = 1
    7       evt7      null     1     // event with no child and appID = 1
    8       evt8      null     1     // parent event with appID = 1 and has a child

I tried lot of examples and read a lot of solutions here but i didn't find a problem like this. Can you help me to write the right SQL ?

thx.

Giorgos Betsos · Accepted Answer · 2016-03-18T16:05:12.533

3

Try this:

SELECT DISTINCT COALESCE(e2.eventID, e1.eventID), 
       COALESCE(e2.eventName, e1.eventName),
       COALESCE(e2.appFK, e1.appFK)
FROM events AS  e1
LEFT JOIN events AS e2 ON e1.parentID = e2.eventID AND e1.appFK = 1
WHERE (e1.appFK = 1 AND e1.parentID IS NULL) OR (e2.eventID IS NOT NULL)

The LEFT JOIN fetches parent records (e1.parentID = e2.eventID) of a child having appID = 1 (e1.appFK = 1).

The WHERE clause selects root records having appID = 1 and root records that are related to a child having appID = 1 (e2.eventID IS NOT NULL).

Demo here

edited Mar 18 '16 at 16:05

answered Mar 18 '16 at 16:00

Giorgos Betsos

71,379
9
63
98

thanks a lot for the answer, it works very fine. but my problem is more complicated then my example here. i can't do `LEFT JOIN events AS e2 ON e1.parentID = e2.eventID AND e1.appFK = 1` because the condition appID is a variable, there will be other conditions like environementID = 25 or responsableID = 45 which are the filter values selected dynamically by the user. So i have to add all these conditions dynamically after LEFT JOIN, and it's going to be complicated like this way. – cglvli Mar 18 '16 at 16:31
i deleted the condition `1.appFK = 1` in `LEFT JOIN` and modified the where clause like this `WHERE e1.appFK = 1 AND (e1.parentID IS NULL OR e2.eventID IS NOT NULL)` it works too. Thanks again. – cglvli Mar 18 '16 at 17:01

sql - get parentID with or without child in self refrenced table

1 Answers1