Joining three tables

[emozley]

I have three tables which I intend to use to allow someone to specify a department, category and subcategory for a record that is stored in another table.

DocsDept
--------
DocsDeptID (Autonumber)
DocsDept (Text)

DocsCat
-------
DocsCatID (Autonumber)
DocsDept (Number)
DocsCat (Text)

DocsSubCat
----------
DocsSubCatID (Autonumber)
DocsDept (Number)
DocsCat (Number)
DocsSubCat (Text)

I am trying to build a three level interdependent menu and so my plan is to read the three tables into a recordset and then use some client side javascript to do the menus.

So far for my query I have:

SELECT DocsDept.DocsDept, DocsCat.DocsCat, DocsSubCat, DocsDept.DocsDeptID AS DocsDeptID, DocsCat.DocsCatID AS DocsCatID, DocsSubCat
(FROM DocsCat RIGHT JOIN DocsDept ON DocsCat.DocsDept=DocsDept.DocsDeptID) RIGHT JOIN DocsSubCat ON DocsCat.DocsCatID=DocsSubCat.DocsCat
ORDER BY DocsDept.DocsDept, DocsCat.DocsCat;

But I am getting an error 'Syntax error (missing operator)'. Any ideas where I am going wrong? The query should basically produce something like this, listing all departments, categories and all subcategories with their relevant ID's:

| DocsDept | DocsCat | DocsSubCat | DocsDeptID | DocsCatID | DocsSubCatID |

Thanks very much

Ed

 

[PHV]

Just a syntax fix, replace this:
(FROM DocsCat RIGHT JOIN
with this:
FROM (DocsCat RIGHT JOIN

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[emozley]

I'm now getting a 'join error not supported' error message.

 

[emozley]

I tried building it using acess then going to SQL view and it produced the following which seems to work:

SELECT DocsDept.DocsDeptID, DocsDept.DocsDept, DocsCat.DocsCatID, DocsCat.DocsCat, DocsSubCat.DocsSubCatID, DocsSubCat.DocsSubCat
FROM (DocsDept LEFT JOIN DocsCat ON DocsDept.DocsDeptID = DocsCat.DocsDept) LEFT JOIN DocsSubCat ON DocsCat.DocsCatID = DocsSubCat.DocsCat;

Not sure what the difference between a right join and a left join is but it seems to have worked!

 

[PHV]

And what about this ?
SELECT D.DocsDept, C.DocsCat, S.DocsSubCat, D.DocsDeptID, C.DocsCatID, S.DocsSubCatID
FROM (DocsCat AS C
INNER JOIN DocsDept AS D ON C.DocsDept=D.DocsDeptID)
INNER JOIN DocsSubCat AS S ON C.DocsDept=S.DocsDept AND C.DocsCatID=S.DocsCat
ORDER BY 1, 2

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lespaul]

Not sure what the difference between a right join and a left join is but it seems to have worked!

read this: Understanding SQL Joins

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design