SQL NOT IN question

Question

Post reply

SQL NOT IN question

m.i.k.e

Grasshopper

Points: 16
More actions
March 3, 2015 at 8:21 am

#151404

Hi,
I am working on a few exercises in SQL but I can't seem to finish the last one. I added the question in a picture and the code below is the code I have written so far.
SELECT distinct name
FROM Persons P, TakesClasses T, Knows K
WHERE K.id NOT IN (SELECT personA_id
FROM Knows)

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

laurie-789651 SSCertifiable Points: 7680 More actions · Answer 1

Try this:

--==TEST DATA ==--

use tempdb

go

drop table Persons

drop table Knows

drop table TakesClasses

create table Persons (id int, name varchar(50))

insert into Persons values (1, 'Fred'), (2, 'Bill'), (3, 'Mavis'), (4, 'Arthur'), (5, 'Jill'), (6, 'Anne')

create table Knows (id int, personA_id int, personB_id int)

insert into knows values (1, 1, 2), (2, 1, 3), (3, 3, 4), (4, 3, 5), (5, 5, 2), (6, 5, 6), (7, 2, 5)

create table TakesClasses (id int, person_id int, class_id int)

insert into TakesClasses values (1, 1, 9), (2, 3, 9), (3, 4, 9)

--== TRY THIS ==--

SELECT p.id, p.name

FROM Persons P

WHERE P.id NOT IN (SELECT K.personA_id

FROM Knows K

INNER JOIN TakesClasses TC ON TC.person_id=K.personB_id)

Make a list of persons who know someone taking a class, select persons not in the list.

Luis Cazares SSC Guru Points: 183698 More actions · Answer 2

m.i.k.e (3/3/2015)
Hi,
I am working on a few exercises in SQL but I can't seem to finish the last one. I added the question in a picture and the code below is the code I have written so far.
SELECT distinct name
FROM Persons P, TakesClasses T, Knows K
WHERE K.id NOT IN (SELECT personA_id
FROM Knows)

I understand that you're learning, so please avoid those type of joins. You just made a cartesian product that can kill a server which you are trying to hide by using the distinct. This is because you didn't include any join conditions. If you have, for example, 10, 5 and 30 rows on each table, you're creating 1,500 rows.

Using the joins in Laurie's code, will prevent the creation of accidental cartesian products (can still happen) and will allow you to create outer joins.

Try to understand Laurie's solution and the logic to obtain it.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

m.i.k.e Grasshopper Points: 16 More actions · Answer 3

m.i.k.e

Grasshopper

Points: 16

March 3, 2015 at 10:06 am

#1781437

Yes I will, thank you for the help!