访问SQL LEFT JOIN替代 [英] Access SQL LEFT JOIN alternative

问题描述

我正在尝试使用LEFT JOIN在通过SQL的Access中进行某些操作，但是它似乎无法正常工作. Access不断生成错误不支持JOIN表达式.".

I'm trying something in Access via SQL using a LEFT JOIN but it doesn't seem to be working. Access keeps generating the error "JOIN expression not supported.".

我要完成的工作如下.我有一张有工作卡的桌子，另一张有费用的桌子，如下.

What I'm trying to accomplish is as follows. I have a table with job cards and another table with costs as below.

JOBCARDS
ID    JOBNAME
1     Job one
2     Job two
3     Job three

COSTS
ID    TYPE    COST    JOB
1     PART    15.01   1
2     LABOUR  20.00   1
3     LABOUR  40.00   2
4     PART    34.54   3
5     PART    84.67   3

我正在尝试制定一个SQL查询，该查询将给我以下结果:

I'm trying to formulate an SQL query that will give me the following result:

QUERY
ID    JOBNAME    PARTS    LABOUR
1     Job one    15.01    20.00
2     Job two    0.00     40.00
3     Job three  119.21   0.00

我想出了什么:

SELECT
    CARDS.[ID] AS [ID],
    CARDS.[JOBNAME] AS [JOBNAME],
    SUM (COSTS1.[COST]) AS [PARTS],
    SUM (COSTS2.[COST]) AS [LABOUR]
FROM
    (([JOBCARDS] CARDS LEFT JOIN [COSTS] COSTS1 ON COSTS1.[JOB]=CARDS.[ID] AND COSTS1.[TYPE]='PART')
    LEFT JOIN [COSTS] COSTS2 ON COSTS2.[JOB]=CARDS.[ID] AND COSTS2.[TYPE]='LABOUR')
GROUP BY
    CARDS.[ID], CARDS.[JOBNAME];

访问似乎在"COSTS1.[TYPE] ='PART'"部分出现问题.

Access seems to be having problems with the part "COSTS1.[TYPE]='PART'".

有没有什么方法可以在不使用LEFT JOIN的情况下完成我想做的事情? 还是有人发现错误?

Is there any way I can accomplish what I'm trying to do without using a LEFT JOIN? Or does anyone spot the error?

推荐答案

此SQL将给出结果.
0值将为Null，但是您可以使用NZ将其替换为0.

This SQL will give the result.
The 0 values will be Null, but you could use NZ to replace that with a 0.

SELECT      JobCards.ID
            ,JobName
            ,SUM(C2.Cost) AS Parts
            ,SUM(C1.Cost) AS Labour
FROM        (JobCards LEFT JOIN Costs C1 ON (JobCards.ID = C1.Job AND C1.Type = 'Labour'))
                      LEFT JOIN Costs C2 ON (JobCards.ID = C2.Job AND C2.Type = 'Part')
GROUP BY    JobCards.ID
            ,JobName

修改: 重新阅读您的SQL-您只是忘了在Join的ON语句后加上括号:
(COSTS1.[JOB]=CARDS.[ID] AND COSTS1.[TYPE]='PART')

Re-reading your SQL - you just forgot to put the brackets after the ON statements in your Join:
(COSTS1.[JOB]=CARDS.[ID] AND COSTS1.[TYPE]='PART')

SELECT
    CARDS.[ID] AS [ID],
    CARDS.[JOBNAME] AS [JOBNAME],
    SUM (COSTS1.[COST]) AS [PARTS],
    SUM (COSTS2.[COST]) AS [LABOUR]
FROM
    (([JOBCARDS] CARDS LEFT JOIN [COSTS] COSTS1 ON (COSTS1.[JOB]=CARDS.[ID] AND COSTS1.[TYPE]='PART'))
    LEFT JOIN [COSTS] COSTS2 ON (COSTS2.[JOB]=CARDS.[ID] AND COSTS2.[TYPE]='LABOUR'))
GROUP BY
    CARDS.[ID], CARDS.[JOBNAME];

编辑2 :无需在所有内容之间使用方括号，如果字段名称与源字段相同，则无需为字段名称加上别名，也无需使用SELECT&中的表名称. WHERE子句，除非字段名称出现在多个表中.

Edit 2: There's no need to put brackets around everything, or alias the field names if they're the same as the source field, or use the table names in the SELECT & WHERE clauses unless the field name appears in more than one table.

这篇关于访问SQL LEFT JOIN替代的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！