My "IS NOT" <> working!

[FKlusmann]

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: 7.1.2.2 (x86) Windows 10.0 Firebird Embedded

[Ratslinger]

Hello,

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

[FKlusmann]

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