需要简单的sql指导

dgiusagp  于 2021-07-29  发布在  Java
关注(0)|答案(3)|浏览(283)

我试图为access表编写adosql语句,但得到的结果是错误的。
员工表

ID      Name    DriverID
1   Alex    1
2   Tom     2
3   Trevor  3
4   PHIL    0
5   Gina    4

车辆表

ID  PLATE   EMPLOYEEID  INSERVICE
1   123XYZ  1       N
2   456GFR  2       Y
3   TFV4FG  3       Y
4   F6GK7D  4       Y
5   GEY7GH  1       Y

我想要所有雇员的结果,并显示车辆信息,如果他们分配给它。

Result should be
Name    Plate
Alex    GEY7GH
Tom     456GFR
Trevor  TFV4FG
PHIL    
Gina    F6GK7D

SELECT Employee.ID, Employee.FirstName, Vehicles.Plate, Vehicles.InService
FROM Employee LEFT JOIN Vehicles ON Employee.ID = Vehicles.DriverID
WHERE (((Vehicles.InService)=True));

不显示未分配给车辆的phil。

dxpyg8gm

dxpyg8gm1#

只需在连接中添加条件,确保在使用常量或简单equals以外的任何内容连接时使用括号以避免问题:

SELECT Employee.ID, Employee.FirstName, Vehicles.Plate, Vehicles.InService
FROM Employee LEFT JOIN Vehicles ON (Employee.ID = Vehicles.DriverID AND Vehicles.InService = True)
qeeaahzv

qeeaahzv2#

从上表来看,employee表中的driverid列似乎与vehicles表中的employeeid列对齐,问题是 on 连接中的子句。

SELECT 
          Employee.ID
          ,Employee.FirstName
          ,Vehicles.Plate
          ,Vehicles.InService
FROM 
         Employee 
         LEFT JOIN Vehicles      ON Employee.DRIVERID = Vehicles.EMPLOYEEID
WHERE 
         (((Vehicles.InService)=True));
8hhllhi2

8hhllhi23#

在一个普通的数据库里 WHERE 条件 ON 条款。但我不认为ms access支持这一点:

SELECT e.ID, e.FirstName, v.Plate, v.InService
FROM Employee as e LEFT JOIN
     Vehicles as v
     ON e.ID = v.DriverID AND v.InService = True;

另一种方法是子查询:

SELECT e.ID, e.FirstName, v.Plate, v.InService
FROM Employee as e LEFT JOIN
     (SELECT v.*
      FROM Vehicles as v
      WHERE v.InService = True
     ) as v
     ON e.ID = v.DriverID ;

相关问题