Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 558
  • Last Modified:

Help with SQL query

I'm a web developer with limited database experience, and I need some help writing a SQL query, please.  As far as I can tell, I think I need a query that does an OUTER JOIN with nested INNER JOINs; however, Access 2007 tells me that I can't do that.  I'm assuming that I could probably write the INNER JOIN piece as a separate query and then use that in the OUTER JOIN query, but that puts me in way over my head; thus, I need some help.  

I've attached a screenshot of the database relationships to help with visualization of what I'm trying to do.  

The database is a document management database.  People submit documents, and sometimes other people submit responses to those documents.  For example, let's say someone submits an "Information Request" - in that case CategoryName = Info, CategorySub1Name = Request, and CategorySub2Name = NULL.  Then, let's say someone else submits a "Response" to the previously mentioned "Information Request" - in that case CategoryName = Info, CategorySub1Name = Request, and CategorySub2Name = Response.  In addition, there can be multiple "Responses" to a given "Information Request".  So, you can think of the "Document" table as representing the "Information Requests" and the "Document_1" table as representing the "Responses".  Note, however, that the "Document_1" table was automatically created by Access 2007, and it is really just some kind of virtual instance of the "Document" table or something like that.  If you can explain this phenomenon then I'd be very interested to understand what is going on with this virtual table, but for now I just know that it works.  As you can see, the "Responses" can have all of the same attributes as the "Information Requests" - the differentiating factor is the assigned category.  

So, I need to generate a query that lists out all of the "Information Request" documents along with all of the "Response" documents that correspond to each request.  Visually, the needed query output would be something like this:  

InformationRequest1     ResponseA     ResponseB     ResponseC
InformationRequest2
InformationRequest3     ResponseA
InformationRequest4     ResponseA     ResponseB
InformationRequest5
InformationRequest6     ResponseA

Also, if it matters, I'll be up-converting the database to MS SQL Server 2008 for the web application. I did a test run already, and it all seems to work fine.

Thanks in advance for any help that you can provide!
DocDB-Relationships.JPG
0
whc4
Asked:
whc4
  • 2
  • 2
1 Solution
 
Kevin CrossChief Technology OfficerCommented:
The Document_1 table is just an alias to Document as you pointed out.  It does that because your Document_Responder associative table references Document table in both the key field DocumentID and ResponseDocID.

Something like this should work as a first step to get each request and response matched up.


SELECT req.DocumentTitle, resp.DocumentTitle
FROM Document req
LEFT OUTER JOIN (Document_Responder d_r INNER JOIN Document resp ON resp.DocumentID = d_r.ResponseDocID) ON req.DocumentID = d_r.DocumentID 
WHERE req.CategoryName = 'Info'
AND req.CategorySub1Name = 'Request'
AND req.CategorySub2Name IS NULL;

Open in new window

0
 
Kevin CrossChief Technology OfficerCommented:
If there are documents linked through ResponseDocID that may be in other categories, then this would be more appropriate:

SELECT req.DocumentTitle, resp.DocumentTitle
FROM Document req
LEFT OUTER JOIN (Document_Responder d_r INNER JOIN Document resp ON resp.DocumentID = d_r.ResponseDocID) ON req.DocumentID = d_r.DocumentID 
AND req.CategoryName = resp.CategoryName
AND req.CategorySub1Name = resp.CategorySub1Name 
AND req.CategorySub2Name = 'Response'
WHERE req.CategoryName = 'Info'
AND req.CategorySub1Name = 'Request'
AND req.CategorySub2Name IS NULL;

Open in new window

0
 
whc4Author Commented:
The first solution seems to work with the upconverted SQL Server 2008 version of the database, but it doesn't work on the Access 2007 version of the database.  I get the "JOIN expression not supported" error with Access 2007...I guess it's because of the INNER JOIN nested within the OUTER JOIN.  Thanks!!!

The second solution doesn't give the desired results - or maybe I just don't understand it at all...which is likely.  :)  Basically, it returns about half as many rows (349 vs. 791) and all of the 2nd column results are NULL.  The first solution returns 163 2nd column NULLs.
0
 
whc4Author Commented:
I think this will do the trick.  Thanks for your help!
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now