SQL question re: NOT IN

[gwlam12]

I think this is a silly question but I'd like to go home..



The belowabove is a simple SQL statement. form_field has 20,000+ distinct rows. application has 97 distinct rows. When I run it, I get 0 rows resulting. Why?

 

[KLin]

all 97 of those form_id values are in the the application table. Change NOT IN to IN and you'll probably get 97 results.

 

[Brett]

try:

SELECT DISTINCT form_id FROM form_field

and

SELECT DISTINCT application.form from application


... I'll bet they are the same 🙂

 

[drebo]

What exactly are you trying to do?

It looks like you're trying to find all form fields that are not currently associated with a form. If that's the case, a left outer join where application.form = null would probably get you what you're looking for...assuming application.form is the foreign key for form_field.form_id, of course.

If it returns 0 rows, that likely means that there are no orphaned form_field entities...which should be the case, assuming a 3rd normal form...otherwise, you've got bigger issues with your data model.