The status name is in the status table so you have to join the two tables.
SELECT Documents.Filename, Status.Name
FROM Documents CROSS JOIN Status
WHERE Filename like 'SUM-004.xml'
Deleted = 0
Thanks Wayne. That was close enough to get my guided in the right direction. I made some modifications and this seemed to work:
select Documents.Filename as Project,Status.Name as Status
left join Status
order by Documents.CurrentStatusID
Actually, you don't want to use a cross join here (or in most cases).
I would suggest using 'Design Query in Editor' and let the system create the joins for you automatically.
The Documents table contains the current status of the file in the CurrentStatusID column (which is a number).
This needs to be joined to the StatusID column in the Status table so that you can get the associated Name.
See attached video to see how it's done.
BasicQuerywithJoin.zip 7.6 MB
Thank you Joy.
I did use Design Query in Editor. I just did not notice that it did not automatically join the two table. I had used it before where two column names matched so it automatically created the INNER JOIN. I should have looked closer at my solution before I posted.
Your video did teach me an easier way to create the INNER JOIN. I would just key it in.
Unless there are keys that tie the tables together you won't get a join automatically. Having said that, you also have to be careful because sometimes you get joins that you do not want to use.
For example: Let's say you want a report that lists the following for all the documents in the vault:
- folder path
- current document status (name)
This requires the use of 4 tables:
- Documents (contains the filename)
- Projects (contains the folder path)
- DocumentsInProjects (contains the information detailing which documents are in which folders (projects))
- Status (contains the status name)
When you add the tables you will get a join between the Documents and the Projects table that is not needed (Projects.ProjectID to Document.LockProject) and also between the Projects table and Status table (Projects.StatusID to Status.StatusID). Simply delete the joins you do not need and manually create the required joins.
See attached video.
documentinfo.zip 3.2 MB