Query with a calculated field based on another table in Access-CodePudding

I have two Microsoft Access tables with the following values:

Devices

| N    | Desc   | O | P |
 ------ -------- --- --- 
| 3560 | ABC    | 0 | 0 | <-
| 3559 | DEF    | 0 | 1 |
| 3558 | GHI    | 1 | 0 |
| 3557 | JKL    | 1 | 0 |
| 3548 | MNO    | 0 | 0 | <-
| 3549 | PQR    | 0 | 0 | <-
| 3540 | STU    | 0 | 0 | <-

Notifications

id | N    | Email       |
--- ------ ------------- 
1  | 3559 | [email protected]   | <-
2  | 3548 | [email protected]   | <-
3  | 3548 | [email protected]   |
4  | 3547 | [email protected]   |
5  | 3549 | [email protected]   |

I would like to extract all the Devices records with O = 0 and P = 0 and add a calculated field named Subscribed. This field must be True only if the same N exists in the Notifications table with an Email that I supply as parameter and False otherwise.

Having for example Email = [email protected] parameter (i.e. hardcoded in the SQL for this purpose), then this should be the required result:

| N    | Desc   | O | P | Subscribed |
 ------ -------- --- --- ------------ 
| 3560 | ABC    | 0 | 0 | False      |
| 3548 | MNO    | 0 | 0 | True       |
| 3549 | PQR    | 0 | 0 | False      |
| 3540 | STU    | 0 | 0 | False      |

How can I do this query in Access considering that the two tables might be large in the future?

CodePudding user response：

Probably I missed the subquery SQL, this works:

SELECT Devices.N, Devices.Desc, Devices.O, Devices.P,
(SELECT Count(*) FROM Notifications
WHERE Notifications.N = Devices.N
AND Notifications.Email = "[email protected]") > 0 AS Subscribed
FROM Devices
WHERE ((Devices.O=0) AND (Devices.P=0))

EDIT: After @iDevlop comment I was able to create the LEFT JOIN variant correctly, thanks!

SELECT Devices.N, Devices.Desc, Devices.O, Devices.P, Not IsNull(Id) AS Subscribed
FROM Devices
LEFT JOIN (SELECT Notifications.N, Notifications.Id
FROM Notifications
WHERE (Notifications.Email = "[email protected]")) AS qNotif
ON Devices.N = qNotif.N
WHERE ((Devices.O=0) AND (Devices.P=0));