how do you fetch data in MYSQL foreign key?

Question

   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"

Answer 1

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
)