Find Records from Different Tables

Question

Requirement

Find and display all pilots (employee ID and name) who implemented flights on the planes of 'Commuter' type and ‘Normal’ type.

enter image description here

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

Solution

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';

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

Any idea how to solve it?

your `AND` doesn't work here because you're looking at a single row. In a single row with your join 'commuter' and 'normal' are mutually exclusive. — wax eagle, Nov 19 '12 at 18:54

Taryn · Accepted Answer · 2012-11-19T19:03:57.603

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;

See SQL Fiddle with Demo

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')

See SQL Fiddle with Demo

Good one: HAVING count(distinct plane_new.ptype) = 2; – user311509 Nov 19 '12 at 20:06 — user311509, Nov 19 '12 at 20:06

score 0 · Answer 2 · answered Nov 19 '12 at 19:17

the wrong is here

      WHERE plane_new.ptype = 'Commuter' AND plane_new.ptype = 'Normal';

plane_new.ptype cant have two things ,computer and normal in your database, it must be one of them thats why u get no rows returned. so maybe you mean something else. here in your WHERE statment u can use only OR. and they the rows returned are those who have computer and normal

Find Records from Different Tables

2 Answers2

Linked