asked on

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

ASKER CERTIFIED SOLUTION

Kevin Cross

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Kevin Cross

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

whc4

ASKER

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.

whc4

ASKER

I think this will do the trick. Thanks for your help!