There is a (I assume) simple query i cannot figure out (in a best practice way).
Let's say I have three tables:
- AOI (Areas of Interest)
- Users_AOI (Simply containing userId and AOIId for all interests for each user)
So far so good.
Now I want to select all users that have interest of 'x' and 'y' and 'z'. Simple right? How do I do it?
My original attempt was
SELECT Users.Id, Users.FN, etc FROM Users left join Users_AOI on User.ID = Users_AOI.UserId WHERE Users_AOI.AOIID = x and Users_AOI.AOIID = y and Users_AOI.AOIID = z
But ofcourse it returns NOTHING because there is no AOIID that equals x, y, and z at the same time.
What would be the "Best Practice Way" of handling these type of queries?
Any help will be highly appreciated.