raymurphy
asked on
Adding LEFT JOIN to existing LEFT JOINs in Access
Completely new to Access development (coming from SQL Server development) and have been dragged into looking at an Access problem.
An Access front-end report uses the following code which works OK :
SELECT [Report: GetAuthNumber].STRINGVALUE AS Auth, jiraissue.pkey AS [Jira Issue], jiraissue.SUMMARY, worklog.CREATED, worklog.AUTHOR AS Name, [timeworked]/3600 AS [Time]
FROM worklog LEFT JOIN (jiraissue LEFT JOIN [Report: GetAuthNumber] ON jiraissue.pkey = [Report: GetAuthNumber].pkey)
ON worklog.issueid = jiraissue.ID
WHERE (((worklog.CREATED) Between [Start Date] And [End Date]))
ORDER BY [Report: GetAuthNumber].STRINGVALUE , jiraissue.pkey;
I need to anend that report to include an additional LEFT JOIN against the jiraissue table, and to pick up an additional column in the SELECT column list.
So the new column to be picked up is :
CONCAT_WS('-',project_key. project_ke y, issuenum) AS 'Jira Issue'
and the additional LEFT JOIN is :
LEFT JOIN (project_key ON jiraissue.project = project_key.project_id)
I therefore amended the code so that it now reads :
SELECT [Report: GetAuthNumber].STRINGVALUE AS Auth, jiraissue.pkey AS [Jira Issue], CONCAT_WS('-',project_key. project_ke y, issuenum) AS 'Jira Issue', jiraissue.SUMMARY, worklog.CREATED, worklog.AUTHOR AS Name, [timeworked]/3600 AS [Time]
FROM worklog LEFT JOIN (jiraissue LEFT JOIN [Report: GetAuthNumber] ON jiraissue.pkey = [Report: GetAuthNumber].pkey)
LEFT JOIN (project_key ON jiraissue.project = project_key.project_id)
ON worklog.issueid = jiraissue.ID
WHERE (((worklog.CREATED) Between [Start Date] And [End Date]))
ORDER BY [Report: GetAuthNumber].STRINGVALUE , jiraissue.pkey;
But on saving the report I get 'Syntax error in FROM Clause'.
Please pardon my ignorance on Access syntax, but can anyone please advise on what I'm doing wrong ?
Thanks
Ray
An Access front-end report uses the following code which works OK :
SELECT [Report: GetAuthNumber].STRINGVALUE
FROM worklog LEFT JOIN (jiraissue LEFT JOIN [Report: GetAuthNumber] ON jiraissue.pkey = [Report: GetAuthNumber].pkey)
ON worklog.issueid = jiraissue.ID
WHERE (((worklog.CREATED) Between [Start Date] And [End Date]))
ORDER BY [Report: GetAuthNumber].STRINGVALUE
I need to anend that report to include an additional LEFT JOIN against the jiraissue table, and to pick up an additional column in the SELECT column list.
So the new column to be picked up is :
CONCAT_WS('-',project_key.
and the additional LEFT JOIN is :
LEFT JOIN (project_key ON jiraissue.project = project_key.project_id)
I therefore amended the code so that it now reads :
SELECT [Report: GetAuthNumber].STRINGVALUE
FROM worklog LEFT JOIN (jiraissue LEFT JOIN [Report: GetAuthNumber] ON jiraissue.pkey = [Report: GetAuthNumber].pkey)
LEFT JOIN (project_key ON jiraissue.project = project_key.project_id)
ON worklog.issueid = jiraissue.ID
WHERE (((worklog.CREATED) Between [Start Date] And [End Date]))
ORDER BY [Report: GetAuthNumber].STRINGVALUE
But on saving the report I get 'Syntax error in FROM Clause'.
Please pardon my ignorance on Access syntax, but can anyone please advise on what I'm doing wrong ?
Thanks
Ray
In general: From entity left join entity
From (entity left join entity) Left Join entity
Or: From entity Left Join (entity left join entity)
Select t1.f1, t2.f2, t3.f3, t4.f4
FROM (( t1 Left Join t2 On t1.x = t2.x)
Left Join t3 On t2.x = t3.x)
Left Join t4 On t3.x = t4.x
Where ...
The whole expression after From behaves as one entity.
Number of opening braces = no of tables - 2
closing braces are inserted around the previous entry, The left join is between the new table and the previous entity.
To add a a table t5 left joined to above sql
Select t5.x, t1.f1, t2.f2, t3.f3, t4.f4
FROM t5 left Join ((( t1 Left Join t2 On t1.x = t2.x)
Left Join t3 On t2.x = t3.x)
Left Join t4 On t3.x = t4.x) On t5.x = t1.x
....
Notice adding one extra table requires extra braces to previous entity.
To have the previous entity left joined to t5:
Select t5.x, t1.f1, t2.f2, t3.f3, t4.f4
FROM ((( t1 Left Join t2 On t1.x = t2.x)
Left Join t3 On t2.x = t3.x)
Left Join t4 On t3.x = t4.x) t5 left Join On t5.x = t4.x ' can be t1.x
....
Check this article dealing with Inner Join.
https://www.experts-exchange.com/Microsoft/Development/MS_Access/A_3597-INNER-JOIN-a-Number-Of-Tables.html
From (entity left join entity) Left Join entity
Or: From entity Left Join (entity left join entity)
Select t1.f1, t2.f2, t3.f3, t4.f4
FROM (( t1 Left Join t2 On t1.x = t2.x)
Left Join t3 On t2.x = t3.x)
Left Join t4 On t3.x = t4.x
Where ...
The whole expression after From behaves as one entity.
Number of opening braces = no of tables - 2
closing braces are inserted around the previous entry, The left join is between the new table and the previous entity.
To add a a table t5 left joined to above sql
Select t5.x, t1.f1, t2.f2, t3.f3, t4.f4
FROM t5 left Join ((( t1 Left Join t2 On t1.x = t2.x)
Left Join t3 On t2.x = t3.x)
Left Join t4 On t3.x = t4.x) On t5.x = t1.x
....
Notice adding one extra table requires extra braces to previous entity.
To have the previous entity left joined to t5:
Select t5.x, t1.f1, t2.f2, t3.f3, t4.f4
FROM ((( t1 Left Join t2 On t1.x = t2.x)
Left Join t3 On t2.x = t3.x)
Left Join t4 On t3.x = t4.x) t5 left Join On t5.x = t4.x ' can be t1.x
....
Check this article dealing with Inner Join.
https://www.experts-exchange.com/Microsoft/Development/MS_Access/A_3597-INNER-JOIN-a-Number-Of-Tables.html
ASKER
Thanks for both replies, very useful information .....
Have now got this version of the code which almost works :
SELECT [Report: GetAuthNumber].STRINGVALUE AS Auth,
[project_key].[project_key ] & '-' & [jiraissue].[issuenum] AS JiraIssueNo,
[jiraissue].SUMMARY,
worklog.CREATED, worklog.AUTHOR AS Name, [timeworked]/3600 AS [Time]
FROM
(
worklog
LEFT JOIN (jiraissue LEFT JOIN [Report: GetAuthNumber]
ON jiraissue.pkey = [Report: GetAuthNumber].pkey)
ON worklog.issueid = jiraissue.ID
)
LEFT JOIN project_key ON jiraissue.PROJECT = project_key.PROJECT_ID
WHERE (((worklog.CREATED) Between [Start Date] And [End Date]))
ORDER BY [Report: GetAuthNumber].STRINGVALUE , jiraissue.pkey;
So I'm happy with that syntax. Where I say 'almost' works, please note the line :
[project_key].[project_key ] & '-' & [jiraissue].[issuenum] AS JiraIssueNo,
With that line, I get an Access error (shown on attached screenshot) saying that
"specified field [jiraissue].[issuenum] could refer to more than one table listed in the FROM clause" ??
If I change the issuenum reference to any other column on the jiraissue table, the report will actually execute, although obviously the output doesn;t make sense - eg I can replace issuenum with description, and the report will actually run.
Can't see why this error should be coming up, as I'm only referencing
[jiraissue].[issuenum] once in the query ?
SNAG-357.gif
Have now got this version of the code which almost works :
SELECT [Report: GetAuthNumber].STRINGVALUE
[project_key].[project_key
[jiraissue].SUMMARY,
worklog.CREATED, worklog.AUTHOR AS Name, [timeworked]/3600 AS [Time]
FROM
(
worklog
LEFT JOIN (jiraissue LEFT JOIN [Report: GetAuthNumber]
ON jiraissue.pkey = [Report: GetAuthNumber].pkey)
ON worklog.issueid = jiraissue.ID
)
LEFT JOIN project_key ON jiraissue.PROJECT = project_key.PROJECT_ID
WHERE (((worklog.CREATED) Between [Start Date] And [End Date]))
ORDER BY [Report: GetAuthNumber].STRINGVALUE
So I'm happy with that syntax. Where I say 'almost' works, please note the line :
[project_key].[project_key
With that line, I get an Access error (shown on attached screenshot) saying that
"specified field [jiraissue].[issuenum] could refer to more than one table listed in the FROM clause" ??
If I change the issuenum reference to any other column on the jiraissue table, the report will actually execute, although obviously the output doesn;t make sense - eg I can replace issuenum with description, and the report will actually run.
Can't see why this error should be coming up, as I'm only referencing
[jiraissue].[issuenum] once in the query ?
SNAG-357.gif
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Please accept my apologies for not getting back regarding this issue. Comments from hnasr are noted and appreciated, but as per my last comment (24 Jan) as far as I can see the code is only making one reference to this 'issuenum' column and that column is not held in any of the other tables used in the query (and [jiraissue].[issuenum] is being named explicitly).
Also, as mentioned the query will work if 'issuenum' is replaced with any other column from the jiraissue table - so I;m completely stumped as to why I get the error when specifying the [jiraissue].[issuenum] column ?
Also, as mentioned the query will work if 'issuenum' is replaced with any other column from the jiraissue table - so I;m completely stumped as to why I get the error when specifying the [jiraissue].[issuenum] column ?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for following this up, and sorry for taking so long to get back ... Will get back to you once I've done further checks ...
ASKER
Still get the same issue where specifying the [jiraissue].[issuenum] column fives the error message about "specified field [jiraissue].[issuenum] could refer to more than one table listed in the FROM clause".
Most puzzling thing is that the query will work if 'issuenum' is replaced with any other column from the jiraissue table it works ......
Anyway, I've hit a brick wall with this, so I'll just have to wrap it here as issue, and split some points with you guys for giving some great and helpful advice - thanks ...
Most puzzling thing is that the query will work if 'issuenum' is replaced with any other column from the jiraissue table it works ......
Anyway, I've hit a brick wall with this, so I'll just have to wrap it here as issue, and split some points with you guys for giving some great and helpful advice - thanks ...
https://www.experts-exchange.com/Microsoft/Development/MS_Access/A_2380-Domain-Aggregate-for-Concatenating-Values-by-Group-in-Microsoft-Access.html
There are quite a few examples included with it, so use one of those for a roadmap on replacing Concat_WS.
Also: You have two columns named "Jira Issue":
jiraissue.pkey AS [Jira Issue]
CONCAT_WS('-',project_key.
When you fix the Concat, you should rename one.
The Join syntax should work, although Access would write it like this:
LEFT JOIN jiraissue ON worklog.issueid = jiraissue.ID
LEFT JOIN [Report: GetAuthNumber] ON jiraissue.pkey = [Report: GetAuthNumber].pkey)
LEFT JOIN (project_key ON jiraissue.project = project_key.project_id)