CODEWITHSUNDEEP
phpmysqlsql
rdelcampog
rdelcampog

Reputation: 3

SQL query to fetch data form another table

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.

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.

Upvotes: 0

Views: 518

Answers (2)

Ejaz
Ejaz

Reputation: 8872

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.

Upvotes: 2

wilku
wilku

Reputation: 36

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

Upvotes: 0

Related Questions