-1

I have the following tables:

Table configs:

id config
1 ip
2 os
3 cpu

Table options

id config_id option
1 1          127.0.0.1
2 1          192.168.0.1
3 2          windows
4 2          linux
5 3          AMD
6 3          Intel

The config_id column in table options is a foreign key to the id field in table configs

I have a third table users_configs that links users with configs and options like so:

Table users_configs

id user_id config_id, option_id
1 123      1          1
2 456      2          2
3 789      3          3

I would like to get all users that have the following both options at the same time:

- os: windows
- cpu: AMD

I tried the following query, but because of the OR, this returns all users that have either os:windows OR cpu: AMD.

SELECT * FROM users_configs 
LEFT JOIN options ON options.id = users_configs.option_id
WHERE (options.config_id = 2 OR options.config_id = 3)

Any help will be appreciated thanks.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
user765368
  • 19,590
  • 27
  • 96
  • 167

2 Answers2

1

You can use joins so you can compare one row of user_configs to another row of user_configs, such that they have the same user_id and then join each of these rows to the respective lookup tables for the os and cpu values.

SELECT u1.user_id
FROM users_configs AS u1
JOIN users_configs AS u2 ON u1.user_id=u2.user_id
JOIN config AS c1 ON u1.config_id=c1.id
JOIN config AS c2 ON u2.config_id=c2.id
JOIN options AS o1 ON u1.option_id=o1.id
JOIN options AS o2 ON u2.option_id=o2.id
WHERE c1.config='os' AND o1.option='windows'
  AND c2.config='cpu' AND o2.option='AMD'
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
0

You can use aggregation:

SELECT uc.user_id
FROM users_configs uc JOIN
     options o
     ON o.id = uc.option_id join
     config c
     ON c.id = uc.config_id
WHERE (c.config = 'os' AND o.option 'windows') OR 
      (c.config = 'cpu' AND o.option = 'AMD')
GROUP BY uc.user_id
HAVING COUNT(DISTINCT c.config) = 2;

The where gets users that have either option. The HAVING guarantees that a user has both.

Ajax1234
  • 69,937
  • 8
  • 61
  • 102
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Won't this only give the specified answer if there is a unique constraint on user_configs(user_id, config_id)? Although the OP did not specify whether there was enforcement of uniqueness on the source data nor the expected behviour of the query if there are non-unique records. – symcbean Mar 30 '21 at 17:46
  • @symcbean . . . I would expect the data to not have duplicates, but nothing in this query requires that. – Gordon Linoff Mar 30 '21 at 18:51