Skip to content
Advertisement

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

I have 2 queries:

and second one base on first one

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

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

Advertisement

Answer

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:

Or, using a common table expression:

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:

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:

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

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

User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement