user --------------------------- userid name --------------------------- 1 ... 2 ... 3 ... item --------------------------- itemid category --------------------------- 1 ... 2 ... 3 ... request --------------------------- requestid itemid userid --------------------------- 1 ... ... 2 ... ... 3 ... ...
I want to fetch all categories in item table that is equal to the categories in request table of a specific user
example a user requests an item with a category of “math”, i want to fetch all items in item table with category of “math”
Advertisement
Answer
You can do this with joins:
select i1.* from request r inner join item i on r.itemid = i.itemid inner join item i1 on i1.category = i.category where r.userid = ?
The logic is select all requests of the given user id, and bring the corresponding items. You can then join one more time for items that belong to the same category.
Depending on your data, this might generate duplicates. If so, exists is probably more appropriate:
select i1.*
from item i1
where exists (
select 1
from request r
inner join item i on r.itemid = i.itemid
where r.userid = ? and i.category = i1.category
)