I have 4 tables.
user_id, username, and name)token_id, user_id). Each user has 10 token entries. Each token is assigned to only one user. A user has multiple tokens (10)arcade_id, token_id, name, levels, lives). Only one token can be assigned to one arcade game only.prize_id, token_id, name, length, width, height). Only one token can be assigned to one prize only.A token is assigned to either an arcade game or a prize or nothing but not both.
My question is how do set-up a MySQL join query to figure out which tokens for a given user are assigned to game or a prize or have not been assigned yet. Are my table layouts optimal? Or do you have suggestions for another layout?
Here are my tables:
mysql> select * from users;
+---------+--------+
| user_id | name |
+---------+--------+
| 1 | User 1 |
| 2 | User 2 |
| 3 | User 3 |
| 4 | User 4 |
+---------+--------+
mysql> select * from tokens;
+----------+---------+
| token_id | user_id |
+----------+---------+
| 1 | 1 |
| 2 | 1 |
| 3 | 2 |
| 4 | 2 |
| 5 | 2 |
| 11 | 2 |
| 6 | 3 |
| 7 | 3 |
| 10 | 3 |
| 8 | 4 |
| 9 | 4 |
+----------+---------+
mysql> select * from prizes;
+----------+----------+-----------+
| prize_id | token_id | prizename |
+----------+----------+-----------+
| 1 | 4 | prize 1 |
| 2 | 7 | prize 2 |
| 3 | 8 | prize 3 |
| 4 | 9 | prize 4 |
+----------+----------+-----------+
mysql> select * from arcade;
+-----------+----------+----------+
| arcade_id | token_id | gamename |
+-----------+----------+----------+
| 1 | 1 | game 1 |
| 2 | 2 | game 2 |
| 3 | 3 | game 3 |
| 4 | 5 | game 4 |
| 5 | 11 | game 6 |
+-----------+----------+----------+
I would like a SQL query where I can get the following info:
For User 1, they have 2 tokens - token id 1 is assigned to game 1 and token id 2 is assigned to game 2
Or for user 2 - they have 4 tokens - token id 3 is assigned to game 3, token id 4 is assigned to prize 1, token id 5 is assigned to game 4.
Or for user 3 - they have 3 tokens - token id 6 is not assigned yet, token id 7 is prize 2, token id 10 is not assigned
and so on.
I would like to build a MySQL query string like this
Select ****
from *****
Where user_id = 1
Where I specify the user id and get all the info above.
Try this query but if there was some data i would be able to check although i have checked with dummy data
select
t.token_id,
IFNULL(g.game,'') as Game,
IFNULL(p.name,'') as Prize,
case when g.game != '' then 'Assigned' when p.name != '' then 'Assigned' else 'Not assigned yet' end as `Status`
from token as t
left join (select *
from games
where token_id not in(select
token_id
from prize)) as g
on g.token_id = t.token_id
left join (select *
from prize
where token_id not in(select
token_id
from games)) as p
on p.token_id = t.token_id
EDITED
Then it should be the most simple thing to do
select *
from `user`
left join token
on user.user_id = token.user_id
left join games
on games.token_id = token.token_id
left join prize
on prize.token_id = token.token_id
where user.user_id = 1