Joins and Relationships in Queries

[Chris Large]

Hi

Something like :-

SELECT [Module Table].*
FROM [Module Table] RIGHT JOIN [Domain Table] ON [Module
Table].Domain_Ref = [Domain Table].Domain_Ref
WHERE [Domain Table].Domain_Ref Is Null;


hth

Chris

 

[Dale Fye]

I think it should be a LEFT Join given the way you have written the
rest of the query. In this case LEFT JOIN means give me all the
records from [Module Table] and only those that match from [Domain
Table].

--
HTH

Dale Fye


Hi

Something like :-

SELECT [Module Table].*
FROM [Module Table] RIGHT JOIN [Domain Table] ON [Module
Table].Domain_Ref = [Domain Table].Domain_Ref
WHERE [Domain Table].Domain_Ref Is Null;


hth

Chris

 

[Tom]

I tried this, but the query returns no matching records,
whereas I would have expected three module records to be
returned (i.e those with either an invalid domain
reference or a blank/null domain reference).


Thanks

Tom

-----Original Message-----
Hi

Something like :-

SELECT [Module Table].*
FROM [Module Table] RIGHT JOIN [Domain Table] ON [Module
Table].Domain_Ref = [Domain Table].Domain_Ref
WHERE [Domain Table].Domain_Ref Is Null;


hth

Chris

-----Original Message-----
I have two tables as follows:

1. Module Table.....
Module_Ref Domain_Ref Module_Name
ACC1 ACC Accounts1
ACC2 ACC Accounts2
ACC3 ACD Accounts3
ACC4 ACC Accounts4
ACC5 ACE Accounts5
ACC6 Accounts6
etc.

2.Domain Table.....
Domain_Ref Domain_Name
ACC Accounting
BUS Business
CHE Chemistry
etc.

At present neither table has a primary key or index.
As you can see, there is one Domain to many Modules.
I want to construct a query that selects those Modules
that have a Domain_Ref which is not a valid Domain (i.e.
not in the Domain Table).
In the example above these would be Modules ACC3, ACC5 and
ACC6.

Any help would be greatly appreciated.
Thank you in advance.

Tom

.

.