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

Question

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?

Sergey Kalinichenko · Answer 1 · 2014-04-08T14:25:18.383

3

You can use existential quantifiers in your SQL conditions, represented by the EXISTS keyword:

SELECT *
FROM TableA a
WHERE NOT EXISTS (        -- No rental record
    SELECT *
    FROM TableB b
    WHERE b.dvdID=a.dvdID -- The record is for this DVD, and...
      AND NOT EXISTS (    -- ...there is no return record
          SELECT * FROM TableC c WHERE c.rentID=b.rentID
      )
)

The query reads almost like a poorly constructed English sentence, but it explains what is going on.

edited Apr 08 '14 at 14:25

answered Apr 08 '14 at 13:58

Sergey Kalinichenko

714,442
84
1,110
1,523

1

If you see the schema and sample dataset there is no direct relationship between tableA and tableC thus this will give no reultset – M Khalid Junaid Apr 08 '14 at 14:01
1

It would be faster to use OUTER JOINs rather than EXISTS – StanislavL Apr 08 '14 at 14:06
@StanislavL This may have been the case a decade ago, but it is no longer even close to the truth. All non-toy RDBMS engines have this figured out long time ago, so an `EXIST` query will be almost as fast as an equivalent query with a join. – Sergey Kalinichenko Apr 08 '14 at 14:10
No, Your subquery is executed for each row in TableA to compare with a.id. You can try to reproduce yourself. – StanislavL Apr 08 '14 at 14:12
Reproduce the case (create a dummy tables and fill them with some significant amount of rows and run your query and write one more query with JOINs). It does not depend on RDBMS. – StanislavL Apr 08 '14 at 14:20
@dasblinkenlight c.id and a.id? there is no a foreign key that connect c to a. could you be more spesific? maybe use fiddle demo – Altiano Gerung Apr 08 '14 at 14:27
@StanislavL I have tried a correlated `EXISTS` a great deal of times, always with extremely satisfactory results. If I can convert my query to avoid a table scan, modern query optimizers are capable of doing it too. Ten..fifteen years ago the situation has been different. – Sergey Kalinichenko Apr 08 '14 at 14:35
well actually i have found the answer by Log1c.. your answer is like ~a & ~b and log1c's answer is like ~a | b. in theory both are diff but in this particular problem both answer can solved it. but anyway thank you for helping me. – Altiano Gerung Apr 08 '14 at 14:49

score 1 · Accepted Answer · answered Apr 08 '14 at 14:03

1

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

answered Apr 08 '14 at 14:03

Ravi Dhoriya ツ

4,435
8
37
48

nice, you're correct sir. maybe it's because you use SQLFiddle that really help alot, thanks anyway :) – Altiano Gerung Apr 08 '14 at 14:34
anyway could you give some tips (maybe references like ebook) to help me learn mySQL? – Altiano Gerung Apr 08 '14 at 14:52
Well, I learned SQL from my syllabus books long back (about 5 year back). You just look for SQL tutorials and by the practice you'l become master in it. – Ravi Dhoriya ツ Apr 08 '14 at 15:00
No tutorial can be compared with "Practice", only practice will make you more confident. Just try examples and go through with `joins, group by, order by, aggregate functions` those are basics of SQL which are commonly used in every DBMS. – Ravi Dhoriya ツ Apr 08 '14 at 15:02
As mentioned, using an outer join performs faster. I have updated the SQLFiddle to demonstrate how to do this. http://sqlfiddle.com/#!2/50272/19 – Derokorian Sep 27 '14 at 16:28

M Khalid Junaid · Answer 3 · 2014-04-08T14:13:35.957

1

From your sample data set it is clear that you need the dvds whose rent exists in TableC for this you can do so a simple INNER join ,last join from table3 will satisfy the rent exist condition for a dvd from table1

select t1.* FROM
Table1 t1
LEFT JOIN Table2 USING(dvdID)
JOIN Table3 USING(rentID)

Fiddle Demo

edited Apr 08 '14 at 14:13

answered Apr 08 '14 at 14:04

M Khalid Junaid

63,861
10
90
118

it should be OUTER join and Table2.id is null to select dvd that not in Table B – StanislavL Apr 08 '14 at 14:07
1

+1 for replacing with LEFT JOIN but also should be WHERE Table2.id is null – StanislavL Apr 08 '14 at 14:13

score 1 · Answer 4 · edited Apr 08 '14 at 14:19

1

try this Option 1

select * from A 
where dvdID not in (Select dvdID from B where rentID not in (select rentID from C))

edited Apr 08 '14 at 14:19

Racil Hilan

24,690
13
50
55

answered Apr 08 '14 at 14:13

Highland

453
1
4
7

Racil Hilan · Answer 5 · 2014-04-08T14:26:10.783

0

Try this:

SELECT TableA.dvdID, TableA.dvdTitle 
FROM TableA
LEFT JOIN TableB ON TableA.dvdID = TableB.dvdID
LEFT JOIN TableC ON TableC.rentID = TableB.rentID
WHERE TableB.rentID IS NULL
   OR (TableB.rentID IS NOT NULL
       AND TableC.returnID IS NOT NULL)

edited Apr 08 '14 at 14:26

answered Apr 08 '14 at 14:13

Racil Hilan

24,690
13
50
55

score 0 · Answer 6 · answered Apr 08 '14 at 14:25

0

You should use JOINS instead of EXISTS / NOT EXISTS

SELECT 
    a.*
FROM TableA a
    LEFT join TableB b
        on b.id=a.id
    LEFT Join TableC c 
        on c.id=a.id
WHERE
    b.id is null 
    OR (b.id is not null AND c.id is not null)

answered Apr 08 '14 at 14:25

Ryx5

1,366
8
10

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

6 Answers6

Fiddle Demo