SQL查询从另一个表中获取数据

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.

you can try following query

SELECT `devices`.`key` 
FROM users 
JOIN preferences ON users.id = preferences.user_id
JOIN user_devices ON users.id = user_devices.user_id
JOIN devices ON user_devices.device_id = devices.id
WHERE preferences.monday = true

obviously you'll have to check and correct the column names of preferences and users table in query to match your database column names.

You can try this (i just hate JOINs ;p):

SELECT DISTINCT(`devices`.`key`)
FROM `devices`,`user_devices`,`preferences`
WHERE `devices`.`id` = `user_devices`.`device_id`
  AND `preferences`.`user_id` = `user_devices`.`user_id`
  AND `preferences`.`monday` = 1

Hope it would help