CODEWITHSUNDEEP
sqlfirebird
batonico
batonico

Reputation: 25

Combining 2 queries in 1 (select based on other select)

I have 2 queries:

SELECT EW_OBIEKTY.STATUS
    , EW_OB_ELEMENTY.IDE
    , EW_OB_ELEMENTY.TYP
FROM EW_OBIEKTY 
INNER JOIN EW_OB_ELEMENTY 
    ON EW_OBIEKTY.UID = EW_OB_ELEMENTY.UIDO
WHERE (((EW_OBIEKTY.STATUS)=0) AND ((EW_OB_ELEMENTY.TYP)<>1));

and second one base on first one

 SELECT EW_POLYLINE.P0_X, EW_POLYLINE.P0_Y, EW_POLYLINE.ID, EW_POLYLINE.STAN_ZMIANY, [**result of 1st one**].IDE, EW_POLYLINE.ID_WARSTWY
FROM EW_POLYLINE LEFT JOIN [**result of 1st one**] ON EW_POLYLINE.ID = [**result of 1st one**].IDE
WHERE (((EW_POLYLINE.STAN_ZMIANY)=0) AND (([**result of 1st one**].IDE) Is Null));

They work nice, but I need them to work as a one combined query, result from access looks like this:

SELECT EW_POLYLINE.ID
    , EW_OB_ELEMENTY.IDE
    , EW_OB_ELEMENTY.TYP
    , EW_OB_ELEMENTY.TYP
    , EW_OBIEKTY.STATUS
    , EW_POLYLINE.STAN_ZMIANY
FROM (EW_POLYLINE 
        LEFT JOIN EW_OB_ELEMENTY 
            ON EW_POLYLINE.ID = EW_OB_ELEMENTY.IDE) 
        INNER JOIN EW_OBIEKTY 
            ON EW_OB_ELEMENTY.UIDO = EW_OBIEKTY.UID
WHERE (((EW_OB_ELEMENTY.IDE) Is Null) 
             AND ((EW_OB_ELEMENTY.TYP)<>1) 
             AND ((EW_OBIEKTY.STATUS)<>3) 
             AND ((EW_POLYLINE.STAN_ZMIANY)=0));

But this gives me only empty table. Can you help me?

Upvotes: 1

Views: 142

Answers (1)

Mark Rotteveel
Mark Rotteveel

Reputation: 109001

Your inner join depends on the right-hand-side table of the left join, this effectively converts that left join to an inner join. This means that the condition ((EW_OB_ELEMENTY.IDE) Is Null) will always be false, and your whole where condition evaluates to false, and the result set is therefor empty.

You need to nest the first query within the second query:

SELECT EW_POLYLINE.P0_X, EW_POLYLINE.P0_Y, EW_POLYLINE.ID, EW_POLYLINE.STAN_ZMIANY, a.IDE, EW_POLYLINE.ID_WARSTWY
FROM EW_POLYLINE 
LEFT JOIN (
    SELECT EW_OBIEKTY.STATUS
        , EW_OB_ELEMENTY.IDE
        , EW_OB_ELEMENTY.TYP
    FROM EW_OBIEKTY 
    INNER JOIN EW_OB_ELEMENTY 
        ON EW_OBIEKTY.UID = EW_OB_ELEMENTY.UIDO
    WHERE EW_OBIEKTY.STATUS = 0 AND EW_OB_ELEMENTY.TYP <> 1
) as a ON EW_POLYLINE.ID = a.IDE
WHERE EW_POLYLINE.STAN_ZMIANY = 0 AND a.IDE Is Null

Or, using a common table expression:

with a as (
    SELECT EW_OBIEKTY.STATUS
        , EW_OB_ELEMENTY.IDE
        , EW_OB_ELEMENTY.TYP
    FROM EW_OBIEKTY 
    INNER JOIN EW_OB_ELEMENTY 
        ON EW_OBIEKTY.UID = EW_OB_ELEMENTY.UIDO
    WHERE EW_OBIEKTY.STATUS = 0 AND EW_OB_ELEMENTY.TYP <> 1
)
SELECT EW_POLYLINE.P0_X, EW_POLYLINE.P0_Y, EW_POLYLINE.ID, EW_POLYLINE.STAN_ZMIANY, EW_POLYLINE.ID_WARSTWY
FROM EW_POLYLINE 
LEFT JOIN a ON EW_POLYLINE.ID = a.IDE
WHERE EW_POLYLINE.STAN_ZMIANY = 0 AND a.IDE Is Null

Alternatively, you could reorder your joins (and replace the left join with a right join) and move some of the conditions from the where to the join clause:

SELECT EW_POLYLINE.P0_X, EW_POLYLINE.P0_Y, EW_POLYLINE.ID, EW_POLYLINE.STAN_ZMIANY, EW_POLYLINE.ID_WARSTWY
FROM EW_OBIEKTY 
INNER JOIN EW_OB_ELEMENTY
    ON EW_OBIEKTY.UID = EW_OB_ELEMENTY.UIDO AND EW_OBIEKTY.STATUS = 0 AND EW_OB_ELEMENTY.TYP <> 1
RIGHT JOIN EW_POLYLINE 
   ON EW_POLYLINE.ID = EW_OB_ELEMENTY.IDE
WHERE EW_POLYLINE.STAN_ZMIANY = 0 AND EW_OB_ELEMENTY.IDE Is Null

You can also change the evaluation order of your joins by moving parentheses from the first join to the second and move some conditions from the where to join condition:

SELECT EW_POLYLINE.P0_X, EW_POLYLINE.P0_Y, EW_POLYLINE.ID, EW_POLYLINE.STAN_ZMIANY, EW_POLYLINE.ID_WARSTWY
FROM EW_POLYLINE 
LEFT JOIN (EW_OB_ELEMENTY 
  INNER JOIN EW_OBIEKTY 
    ON EW_OB_ELEMENTY.UIDO = EW_OBIEKTY.UID 
       AND EW_OB_ELEMENTY.TYP <> 1 
       AND EW_OBIEKTY.STATUS <> 3)
  ON EW_POLYLINE.ID = EW_OB_ELEMENTY.IDE
WHERE EW_OB_ELEMENTY.IDE Is Null 
AND EW_POLYLINE.STAN_ZMIANY = 0

Yet another possibility would be to use a not exists-predicate:

SELECT EW_POLYLINE.P0_X, EW_POLYLINE.P0_Y, EW_POLYLINE.ID, EW_POLYLINE.STAN_ZMIANY, a.IDE, EW_POLYLINE.ID_WARSTWY
FROM EW_POLYLINE
WHERE EW_POLYLINE.STAN_ZMIANY = 0 
AND NOT EXISTS (
    SELECT 1
    FROM EW_OBIEKTY 
    INNER JOIN EW_OB_ELEMENTY 
        ON EW_OBIEKTY.UID = EW_OB_ELEMENTY.UIDO
    WHERE EW_OBIEKTY.STATUS = 0 AND EW_OB_ELEMENTY.TYP <> 1
    AND EW_OB_ELEMENTY.IDE = EW_POLYLINE.ID
)

This last one is probably best because it is - in my opinion - more self-explanatory than the other ones as it more clearly shows what you are querying for (rows from EW_POLYLINE that don't have rows (not exists) that meet a certain condition).

Upvotes: 1

Related Questions