CODEWITHSUNDEEP
sqloracle-database
darby
darby

Reputation: 167

Combine two selects

I have a problem with two sql querys. The one sql selects some ID's and the other one sets the id structur. The problem is I can't get them both to work as one query.

The SQL which sets the structure:

select nstrid 
from t_ousernstr 
where kstrid = 116 And 
Bis is null
Order by nstrid

The id's from that are like:

100
200
300
400

And the SQL which gets the user id's:

SELECT
    T_OUSER.ID                                      AS ID,
    FROM
    T_OUSER,
    T_OUSERNSTR
    WHERE 
            ( T_OUSERNSTR.NSTRID = ANY(//here should be the id's from above))
            (T_OUSERNSTR.VON is null or SYSDATE >=T_OUSERNSTR.VON) and
            (T_OUSERNSTR.BIS is null) and
            (T_OUSERNSTR.BEGINN IS NULL OR T_OUSERNSTR.BEGINN<= SYSDATE) and
            (T_OUSERNSTR.ENDE is null or T_OUSERNSTR.ENDE> SYSDATE)                                 
    Order By T_OUSER.ID;

Upvotes: 0

Views: 70

Answers (1)

MT0
MT0

Reputation: 167972

Use IN:

SELECT o.ID AS ID
FROM   T_OUSER o
       CROSS JOIN
       T_OUSERNSTR n
WHERE  n.NSTRID IN ( select nstrid 
                     from   t_ousernstr 
                     where  kstrid = 116
                     And    Bis is null )
AND    ( n.BIS    IS NULL )
AND    ( n.VON    IS NULL OR SYSDATE >= n.VON    )
AND    ( n.BEGINN IS NULL OR SYSDATE >= n.BEGINN )
AND    ( n.ENDE   IS NULL OR SYSDATE <  n.ENDE   )                                 
ORDER BY o.ID;

or EXISTS:

SELECT o.ID AS ID
FROM   T_OUSER o
       CROSS JOIN
       T_OUSERNSTR n
WHERE  EXISTS (
         SELECT 1
         FROM   t_ousernstr x
         WHERE  x.kstrid = 116
         AND    x.Bis    IS NULL
         AND    ( n.NSTRID = x.NSTRID OR ( n.NSTRID IS NULL AND x.NSTRID IS NULL ) )
       )
AND    ( n.BIS    IS NULL )
AND    ( n.VON    IS NULL OR SYSDATE >= n.VON    )
AND    ( n.BEGINN IS NULL OR SYSDATE >= n.BEGINN )
AND    ( n.ENDE   IS NULL OR SYSDATE <  n.ENDE   )                                 
ORDER BY o.ID;

or to remove the correlated sub-query you could use analytic functions:

SELECT o.ID AS ID
FROM   T_OUSER o
       CROSS JOIN
       ( SELECT n.*,
                MAX( CASE WHEN kstrid = 116 THEN 1 ELSE 0 END )
                  OVER ( PARTITION BY bis, nstrid )
                  AS has_kstrid
         FROM   T_OUSERNSTR n
       ) n
WHERE  n.has_kstrid = 1
AND    ( n.BIS    IS NULL )
AND    ( n.VON    IS NULL OR SYSDATE >= n.VON    )
AND    ( n.BEGINN IS NULL OR SYSDATE >= n.BEGINN )
AND    ( n.ENDE   IS NULL OR SYSDATE <  n.ENDE   )                                 
ORDER BY o.ID;

Upvotes: 1

Related Questions