Menu
I have what seems to be a simple problem, but can not figure out the proper solution via SQL. I'm using postgresql specifically.
Take the following:
SELECT * FROM users INNER JOIN tags ON (tags.user_id = users.id) WHERE tags.name IN ('word1', 'word2')
This does not do what I need. I want to find users whose tags are ONLY included in the list. If the user has a tag that is not in the list, the user should not be included.
'user1' tags: word1, word2, word3
'user2' tags: word1
'user3' tags: word1, word2
Given: word1 and word2. I want to prepare a query that returns 'user2' and 'user3'. 'user1' is excluded because it has a tag that is not in the list.
Hopefully I made this clear. Thanks for your help!
852
To use the WHERE clause to perform the same join as you perform using the INNER JOIN syntax, enter both the join condition and the additional selection condition in the WHERE clause. The tables to be joined are listed in the FROM clause, separated by commas. This query returns the same output as the previous example.
The SQL SELECT statement is used to retrieve records from one or more tables in your SQL database.
Always put the join conditions in the ON clause if you are doing an INNER JOIN . So, do not add any WHERE conditions to the ON clause, put them in the WHERE clause. If you are doing a LEFT JOIN , add any WHERE conditions to the ON clause for the table in the right side of the join.
A CROSS join returns all rows for all possible combinations of two tables.
Relying on COUNT(*) = 2 will require that there can be no duplicates of user_id and name in the tags table. If that's the case, I'd go that route. Otherwise, this should work:
SELECT u.*
FROM users AS u
WHERE u.id NOT IN (
SELECT DISTINCT user_id FROM tags WHERE name NOT IN ('word1', 'word2')
) AND EXISTS (SELECT user_id FROM tags WHERE user_id = u.id)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
