PROBLEM DESCRIPTION:
I have following tables:
OWNED (this table containes the games that somebody owns!)
userid
gameid
WANTED (this table containes the games that somebody is looking for - wanted!)
userid
gameid
Now I want to make a match query (FOR TRADING): for example - A certain user (userid=10577) has 3 games (gameid=4600, 4500, 4400) and that user is looking for a certain game (gameid = 4882). In simple words: Go find a user 'B' that wants a game that this user owns (so 4600 or 4500 or 4400) and at the same time owns the game (4882) that this user is looking for.
The query i've build (this will give you user 'B' and the game that he wants):
SELECT w.userid, w.gameid FROM WANTED WHERE w.gameid IN (SELECT o.gameid FROM OWNED WHERE o.userid = 10577) AND w.userid = (SELECT o.userid FROM OWNED WHERE o.gameid = 4882)
From what i've read in a lot of forums MYSQL cannot handle subselects. Has anyone got an idea to work around this problem with 1 query (maybe with JOINS??)??
thanks,
R.R.
