CODEWITHSUNDEEP
sqlms-accesssubquery
jpw2007
jpw2007

Reputation: 51

Access SQL Subquery in From

I'm learning Access and SQL, but I have a problem using subqueries in the from clause I can't seem to figure out.

Select *
From (LongSubQuery) as a, (LongSubQuery) as b, (LongSubQuery) as c
Where a.field=b.field=c.field;

This works perfectly as long as each of the statements A, B, and C in the from clause returns a record. If the where clause in any of the three statements prevents the return of a record, then none of the statements will return a result. I've tried various NZ and is not null statements with no luck. I'm suspicious it is actually caused by the last line of code making the fields equivalent. Is there any way around this?

Upvotes: 0

Views: 2678

Answers (1)

Barranka
Barranka

Reputation: 21047

First of all, when you do something like select * from A, B, C (where A, B, C are data sets), you are returning the cartesian product of A, B, C; in other words, you will have #(A)*#(B)*#(C) rows (where #(A) is the number of rows in set A). So, of course, if one of the sets is empty, the whole set is empty.

Possible solution: Use unilateral joins:

select *
from 
    (select ...) as a
    left join (select ...) as b on a.aField = b.aField ...
    left join (select ...) as c on b.aField = c.aFiedl ...

left join returns all the rows on the left side of the relation and all the matching rows of the right side of the relation if it is fulfilled, and null values if it is not fulfilled.

Be careful when you make the relations. Be sure you use the fields you need. Notice that in this case you can define the condition you are using in the where clause directly in the join construction.

Upvotes: 3

Related Questions