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
InformationRequest4 ResponseA ResponseB
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!