0

I have three databases:

1.) A database of people, each with a unique number
2.) A database of dates where something happened, each with a unique number
3.) A database of who went to these events, meaning a table with a person number and an event number in the same row. To Query who DID go to work on any given day, I can do this:

 SELECT "people"."last_name",
       "ev_dates"."event_date",
       "attendee"."id",
       "attendee"."ev_id"
FROM   "attendee",
       "people",
       "ev_dates"
WHERE  "attendee"."id" = "people"."id"
       AND "attendee"."ev_id" = "ev_dates"."id"

However, I can't figure out how to query someone who DIDN'T go to an event. It would essentially mean that there is no match between a person and an event in a third table. Sort of SELECT "PEOPLE"."LASTNAME" WHERE NO MATCH BETWEEN EVENTID AND PERSONID EXISTS.

If I were to just change the equals to the not equals, I get a list of all the other days people did show up.

This happens to be libreoffice base.

RF1991
  • 2,037
  • 4
  • 8
  • 17
user1833028
  • 865
  • 1
  • 9
  • 18
  • So cross People by Events (all combinations) and subtract out what you got in your first query above. Normal way to do that is to Left Outer Join all combos to Attendees and filter out the matches with Attendee.ID IS NULLL. – Chris Maurer Mar 28 '22 at 02:46

1 Answers1

0

When we write a query SELECT * FROM table1 LEFT JOIN table2 on table1.id = table2.id we will get all the rows from table1 whether there are matches in table2 or not. All values from table2 will be null for non-matched rows.
This means that we can find the non-matched rows using the test WHERE table2.idIS NULL.
(Obviously you will replace table2.id with the column that you join on.)