My "IS NOT" <> working!

My “IS NOT” <> working!

I am trying to use BG64-BaseGuide.pdf and its Media_without_Macros as a guide.

I want a Form’s table control to show all media available to be loaned. Table “Loan” shows the “out” and “return dates” of items. I modified that table so that “Media_ID” 5 never appears. This is to represent an item which has never been borrowed.

My Query “Q_Media_On_Loan” [SELECT “ID”, “Media_ID”, “Return_Date” FROM “Loan” WHERE “Return_Date” IS NULL;] shows what IS NOT on hand (already out).

I hope that something like [SELECT “Media”.“ID”, “Media”.“Title” FROM “Q_Media_On_Loan”, “Media” WHERE “Q_Media_On_Loan”.“Media_ID” <> “Media”.“ID”;] would show the Media which is not loaned out at the moment.

Note that – WHERE “Q_Media_On_Loan”.“Media_ID” = “Media”.“ID”; shows the 5 ~ out on loan ~ values,
my desired – WHERE “Q_Media_On_Loan”.“Media_ID” <> “Media”.“ID”; shows every record 5 times.

Thanks for teaching us.
LibreOffice Community Version: (x86) Windows 10.0 Firebird Embedded


Yes, what you have is comparing each id to each other id so you are correctly getting multiple results of the same record.

This, as tested, should work for you:

SELECT "Media"."ID", "Media"."Title" FROM  "Media" WHERE "Media"."ID" Not In (Select "Media_ID" from "Q_Media_On_Loan")

I believe you want all media records NOT found in your “Q_Media_On_Loan” query.

Reference → SQL IN Operator

Thank you again, @Ratslinger. Again you have provided a perfect answer and pointed me to a good reference!