[sqlite] Sample SQL code thats beyond me :(

[Luuk]

On 23-5-2015 18:41, Keith Medcalf wrote:
>
> You also lastly mention that the UUID fields are also used in the selection, 
> so the problem statement is really:
>
> Return the projection of Users and Perimeter_Notifications using the common 
> email field as the equijoin key, but return only the results where there is 
> not a Devices record with the email and uuid matching the corresponding 
> fields in Perimeter_Notifications which has Holiday_Mode = 1:
>
> SELECT *
>    FROM Users, Perimeter_Notifications
>   WHERE Users.email = Perimeter_Notifications.email
>     AND NOT EXISTS (SELECT 1
>                       FROM Devices
>                      WHERE Devicess.email = Perimeter_Notifications.email
>                        AND Devices.UUID = Perimeter_Notifications.UUID
>                        AND Holiday_Mode = 1);
>


I would do:

SELECT *
FROM Users
LEFT JOIN Devices
   ON Users.email = Devices.Email
LEFT JOIN Perimeter_Notifications
   ON Users.email = Perimeter_Notifications.email
WHERE (Holiday_Mode = 1 OR Holiday_Mode IS NULL)