Junction Tables and SQL - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Junction Tables and SQL

Jasen Hicks

3 years ago

Hey everyone! I have a database with a main table (IssuesT) that is linked to a number of other tables (DocumentsT, StakeholdersT, LocationsT, etc.) through junction tables (IssuesXDocumentsT) etc. I need to create a report of everything in the database, even if the Junction tables don't have something linked to a particular issue. Going through the query builder I selected all the fields from all the tables and I got an INNER JOIN to output something, but it left some items in the table IssuesT behind since they didn't have anything in downstream tables via the junctions. What's the best way to handle this? I assume its a left join but am getting tripped up on the syntax since I have 7 Junction Tables to deal with.

	Kevin Robertson @Reply 3 years ago
If you have a lot of tables it may be better (and easier) to break the query down into several smaller queries. Show us what you have (screenshots). Also, check out this video: Outer Joins

Jasen Hicks OP @Reply

3 years ago

Yeah, that's what I was thinking. This is what I started trying after the post:

DocumentationQ

SELECT ixd.IssuesID, dt.document
FROM IssuesXDocumentationT AS ixd
LEFT JOIN DocumentationT AS dt ON ixd.DocumentationID = dt.DocumentationID;

Then

IssuesWDocumentation:

SELECT i.IssuesID, i.Title, i.DateEntered, i.DateUpdated, i.Concern, i.Description, i.[Submarine Security Equity], i.StartTime, i.EndTime, i.Status, dq.document
FROM IssuesT as i
LEFT JOIN DocumentationQ as dq
ON i.IssuesID = dq.IssuesID

I was trying to avoid doing this for each junction table. But it might be necessary...

Jasen Hicks OP @Reply

3 years ago

Good news. I ended up making a number of queries that just left joined each other as they iterated down. Now I have a massive table with a lot of duplicates (expected). What's the best way, in your opinion, to setup a report to show this? Ideally, I'd like the the common fields of the main issues table to be a row, then the things that fit under it such as documentation, tags, etc (things that could have multiple values) to show up as like a list of terms, etc.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View