By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,475 Members | 1,361 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,475 IT Pros & Developers. It's quick & easy.

How to return all records (linked and not linked) in the query?

P: 91
Hi,

I have two tables, [Rules] and [Process] which are in a many to many relationship via a junction table[JRule_Process]. It has the following field (relevant ones for this discussion):

[Rules]
RuleID
RuleName

[Process]
ProcessID
ProcessName

[JRule_Process]
JunctionID
RuleID
ProcessID


I have almost 5000 unique [RuleName] and around 100 [Process Name]. Currently, only some of the rules are mapped with processes.
When I am designing a query with the following fields, [RuleName] and [ProcessName], it is only returning results where there is a mapping between the two tables.

What I also want is to see all the records for RuleName where the ProcessName is missing.

How do I design the query so that I can see all records where [ProcessName] is mapped to a [RuleName] and also where it is not ?

Kindly advise.

Many thanks.
Apr 24 '12 #1

✓ answered by beacon

Have you created a left outer join between the [Rules] table and the [JRule_Process] table, then create a left outer join between the [JRule_Process] table and the [Process] table?

This will show every record from the [Rules] table no matter what and only those records from the [Process] table where you've established a link.

Hope this helps,
beacon

Share this Question
Share on Google+
2 Replies


beacon
100+
P: 579
Have you created a left outer join between the [Rules] table and the [JRule_Process] table, then create a left outer join between the [JRule_Process] table and the [Process] table?

This will show every record from the [Rules] table no matter what and only those records from the [Process] table where you've established a link.

Hope this helps,
beacon
Apr 24 '12 #2

P: 91
Awesome. It works. Thank you so much.
Apr 25 '12 #3

Post your reply

Sign in to post your reply or Sign up for a free account.