从不同的表中查找记录

问题描述

要求

1st image is Plane_new table
2nd image is FlightI_new table
3rd image is Employee_new table

解决方案

SELECT flightI_new.eid, employee_new.ename
FROM flightI_new
INNER JOIN employee_new ON flightI_new.eid = employee_new.eid
INNER JOIN plane_new ON flightI_new.pnum = plane_new.pnum
WHERE plane_new.ptype = 'Commuter' AND plane_new.ptype = 'Normal';

该代码可与 OR 一起使用，但由于某种原因，与AND一起使用，我会得到没有行返回".

The code works with OR, but for some reason with AND i get "no rows returned".

有什么办法解决吗?

推荐答案

您的AND不起作用，因为记录不能在同一记录上同时具有两个ptype.但是您应该可以使用类似这样的东西:

Your AND does not work because records cannot have both ptype on the same record. But you should be able to use something like this:

SELECT flightI_new.eid, employee_new.ename
FROM flightI_new
INNER JOIN employee_new
    ON flightI_new.eid = employee_new.eid
INNER JOIN plane_new
    ON flightI_new.pnum = plane_new.pnum
WHERE plane_new.ptype IN ('Commuter', 'Normal')
GROUP BY flightI_new.eid, employee_new.ename
HAVING count(distinct plane_new.ptype) = 2;

请参见带有演示的SQL提琴

您可以使用类似这样的内容:

Of you can use something like this:

SELECT distinct flightI_new."eid", employee_new."name"
FROM flightI_new
INNER JOIN employee_new
    ON flightI_new."eid" = employee_new."eid"
INNER JOIN plane_new
    ON flightI_new."pnum" = plane_new."pnum"
WHERE plane_new."ptype" = 'commuter'
  AND flightI_new."eid" IN (select flightI_new."eid"
                            FROM flightI_new
                            INNER JOIN employee_new
                                ON flightI_new."eid" = employee_new."eid"
                            INNER JOIN plane_new
                                ON flightI_new."pnum" = plane_new."pnum"
                            WHERE plane_new."ptype" = 'normal')

请参见带演示的SQL提琴

这篇关于从不同的表中查找记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！