Ask Your Question
0

My "IS NOT" <> working!

asked 2021-05-04 04:55:26 +0200

FKlusmann gravatar image

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

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
1

answered 2021-05-04 05:51:11 +0200

Ratslinger gravatar image

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

edit flag offensive delete link more

Comments

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

FKlusmann gravatar imageFKlusmann ( 2021-05-04 16:04:31 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2021-05-04 04:55:26 +0200

Seen: 29 times

Last updated: May 04