Search code examples
mysqlsqlmysql-select-db

MYSQL - select TABLE A rows that are not in Table B except the rows are in TABLE C

TABLE A:

dvdID.......dvdTitle

d01..........Avenger

d02..........Avenger

d03..........Spiderman

TABLE B:

rentID.......dvdID

r01...........d01

r02...........d02

r03...........d03

TABLE C:

returnID.......rentID

t01...............r01

i want to select dvd that not in Table B (rented) except it in Table C (returned) so the output should be like this:

OUTPUT:

dvdID.......dvdTitle

d01..........Avenger

could you help me?

Solution

  • Try this, 

    SELECT *
FROM A 
WHERE (NOT EXISTS (SELECT * FROM B WHERE B.dvdID=A.dvdID))
   OR (EXISTS (SELECT * FROM C,B WHERE C.rentID=B.rentID and B.dvdID=A.dvdID))

    here is SQLFiddle