将2个查询合并为1个(根据其他选择进行选择) [英] Combining 2 queries in 1 (select based on other select)

问题描述

我有2个查询:

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, [**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?

推荐答案

您的inner join取决于left join的右侧表，这有效地将left join转换为inner join.这意味着条件((EW_OB_ELEMENTY.IDE) Is Null)总是 为假，而整个where条件的计算结果为false，因此结果集为空.

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

或者，使用公用表格表达式:

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

或者，您可以重新排序联接(并用right join替换left join)并将某些条件从where移到join子句:

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

另一种可能性是使用 not exists -谓词:

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
)

最后一个可能是最好的，因为-在我看来-比其他解释更不言自明，因为它可以更清楚地显示您要查询的内容(EW_POLYLINE中没有行的行()满足一定条件.

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).

这篇关于将2个查询合并为1个(根据其他选择进行选择)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！