I've been trying to build a little complex SQL query to fetch some keys from a table which has no relation with the one in which I need to "ask".
Practical case:
I have four tables: users, devices, preferences and user_devices.
- Table users, there are user information and the user "ID".
- Table devices, there are columns with device information, the device "ID" (device ID) and the device "key" (device internal ID).
- Table preferences, "user ID" and some boolean fields with weekdays (monday, tuesday, wednesday...).
- Table user_devices, assign ID (auto_increment, not important), "user_id" and "device_id" with relations to their respective tables. This tabble is used to assign a device to a user.
The thing is that I need to obtain all the "devices.key" of users that have the preference "monday" set to true in table preferences.
Example: I ask the database for devices with preference monday true and I obtain an array with two device key.
This query fetch the device key of the user 4, but I need to fetch de device key of all the users with "monday" set to true (1) in the database.
SELECT key FROM devices, user_devices WHERE devices.id = user_devices.device_id AND user_devices.user_id = 4
Thank you very much.