Query, join both ways (1 Viewer)

[asp_learner]

Hi,

I have two queries that I am having trouble joining. there are three options and I don't know why we don't have a forth option. to be able to join both ways. to include all in one query and only those that are equal and to include all in the other query and include those that are equal.
It doesn't allow me to click any other kind of options including referential integrity,etc...
Any suggestions,
Thanks.
Eva

 

[Pat Hartman]

The "fourth" option would be "Full Outer Join". You can do this but it requires three queries.

Query1 - left join tbl1 to tbl2
Query2 - right join tbl1 to tbl2
Query3 - union query1 and query2

The reason that you don't see any options referring to referential integrity is that those options are specified at the table level, not for a query. Relationships made via joins in a query are temporary and exist only to define how the db engine should relate the various tables in a query. These joins may or may not be the same as what is defined in the Relationship window. The Relationship window is used to specify joins that are to be used in enforcing referential integrity and handling update and delete operations affecting related tables.

An example of how a different join may be used in a query than what was permanently defined:

Permanent definition:

tbl1       tbl2               tbl3
ERCId ---> ERCId         ---> ERCId
           ERCLOBGroupCd ---> ERCLOBGroupCd
                         ---> ERCLOBDetailCd

For the current query:

tbl1       tbl3               
ERCId ---> ERCId         
           ERCLOBGroupCd
           ERCLOBDetailCd

The query skips over table 2 because there is no data that it needs to obtain from it.

[This message has been edited by Pat Hartman (edited 01-11-2002).]

 

[asp_learner]

Thank you very much. You have been so help ful to me since I started using this forum.

I was hoping not to have to do additional queries but I see I have no choice.

Thanks,
Eva