Query - Show all records on the many side of a 1-many relationship

[Gordon]

I have two tables which I pull records from. One table has a list of sections. The other table is a list of chapters.

One chapter has many sections.

I have the sections table populated, and want to show all of the sections, even if they are not currently assigned to a chapter.

Currently only the sections which have a chapter show up in my query.

I know this is really simple to do ..... I'm just drawing a blank on it!

Any help would be appreciated.

Gordon

 

[Moniker]

Reverse the query and make it a many-to-one relationship. Assuming your chapters table has a "chapter" field one or more "section" fields per chapter, then you left join the from the section table to the chapter table on the section field, and you display the section field from the section table and the chapter field from the chapter table.

The result is all of your sections and the chapter(s) that they are associated with, if any. Sections with a blank in the chapter field have yet to be assigned.

[U]t_Sections[/U]   [U]t_Chapters
[/U]Sec1         Ch1   Sec1
Sec2         Ch1   Sec2
Sec3         Ch2   Sec4
Sec4
Sec5

The query returns:

Sec1   Ch1
Sec2   Ch1
Sec3
Sec4   Ch2
Sec5

 

[neileg]

Last piece of the jigsaw.

You need to use an outer join between your tables. The Access default is an inner join which only returns data where there is matching data in both tables. If you use an outer join (type 2 or 3 in Access terms), you specify that you want all of the records from sections and those records from chapters that match.

 

[Moniker]

I thought I said that?

"...then you left join the from the section table..."

That's a "type 2" in Access. Sorry if it was confusing. Also note that an outer join and a left join are two different things and Access will not inherently handle a true outerjoin like say, SQL Server or DB2 will.

 

[neileg]

I thought I said that?

"...then you left join the from the section table..."

That's a "type 2" in Access. Sorry if it was confusing. Also note that an outer join and a left join are two different things and Access will not inherently handle a true outerjoin like say, SQL Server or DB2 will.

Sorry Moniker. I must have been speed reading and missed that in your post.

But a left join is a specific form of outer join, AFAIK. Left outer, right outer and full outer are the variations. Or am I wrong, since I've never had any formal db training in my life!

 

[Moniker]

Correct on the full terminology. Briefly, here's the quick rundown for everyone (examples below):

INNER JOIN:
In English: Show me all the records only where there is a match in both tables .

LEFT OUTER JOIN
In English: Show me all the records on the left table and only the matching results from the right table.

RIGHT OUTER JOIN:
In English: The reverse of a LEFT OUTER JOIN; Show me all the records on the right table and only the matching results from the left table.

FULL OUTER JOIN:
In English: Return all the matching row from each table, and also return a copy of each non-matching row from both tables.

Sample Output:

INNER JOIN NameTable.ID ON JobTable.ID:

+-----------+ +---------+             ====================
|ID|NAME    | |ID|JOB   |             ID NAME     ID JOB
|--|--------| |--|------| ==========> -- -------- -- -----
|10|Sanders | |20|Sales |             20 Pernal   20 Sales
|20|Pernal  | |30|Clerk |             30 Marenghi 30 Clerk
|30|Marenghi| |30|Mgr   |             30 Marenghi 30 Mgr
+-----------+ |40|Sales |
              |50|Mgr   |
              +---------+

LEFT JOIN NameTable.ID ON JobTable.ID:

+-----------+ +---------+            ======================
|ID|NAME    | |ID|JOB   |            ID NAME     ID JOB
|--|--------| |--|------| =========> -- -------- -- -----
|10|Sanders | |20|Sales |            10 Sanders   - -
|20|Pernal  | |30|Clerk |            20 Pernal   20 Sales
|30|Marenghi| |30|Mgr   |            30 Marenghi 30 Clerk
+-----------+ |40|Sales |            30 Marenghi 30 Mgr
              |50|Mgr   |
              +---------+

RIGHT JOIN NameTable.ID ON JobTable.ID:

+-----------+ +---------+            =======================
|ID|NAME    | |ID|JOB   |            ID NAME     ID JOB
|--|--------| |--|------| =========> -- -------- -- -----
|10|Sanders | |20|Sales |            20 Pernal   20 Sales
|20|Pernal  | |30|Clerk |            30 Marenghi 30 Clerk
|30|Marenghi| |30|Mgr   |            30 Marenghi 30 Mgr
+-----------+ |40|Sales |            -  -        40 Sales
              |50|Mgr   |            -  -        50 Mgr
              +---------+

FULL OUTER JOIN NameTable.ID ON JobTable.ID (not inherently there in Access,
but can be done by combining the results of a LEFT and RIGHT query)

+-----------+ +---------+            ======================
|ID|NAME    | |ID|JOB   |            ID NAME     ID JOB
|--|--------| |--|------| =========> -- -------- -- -----
|10|Sanders | |20|Sales |            10 Sanders   - -
|20|Pernal  | |30|Clerk |            20 Pernal   20 Sales
|30|Marenghi| |30|Mgr   |            30 Marenghi 30 Clerk
+-----------+ |40|Sales |            30 Marenghi 30 Mgr
              |50|Mgr   |             - -        40 Sales
              +---------+             - -        50 Mgr

HTH

 

[neileg]

Thanks, Moniker. My education advances!

 

[Gordon]

Thanks.... Inner and Outer Joins.. Data Selection in Query

Moniker and Neileg,

Thanks so much for all of the great answers. The translation from Access to English was very helpful!

Gordon

 

[Moniker]

Glad it helped.