mysql连接表 [英] mysql join table

问题描述

我有4张桌子.

1. 具有用户的表A-(user_id, username, and name)
2. 具有令牌-(token_id, user_id)的表B.每个用户有10个令牌条目.每个令牌仅分配给一个用户.一个用户有多个令牌(10)
3. 表C是街机游戏-(arcade_id, token_id, name, levels, lives).只能将一个令牌分配给一个街机游戏.
4. 表D是奖品-(prize_id, token_id, name, length, width, height).只能将一个令牌分配给一个奖项.

1. Table A that has users - (user_id, username, and name)
2. Table B that has tokens - (token_id, user_id). Each user has 10 token entries. Each token is assigned to only one user. A user has multiple tokens (10)
3. Table C that is arcades games - (arcade_id, token_id, name, levels, lives). Only one token can be assigned to one arcade game only.
4. Table D that is prizes - (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.

我的问题是如何设置MySQL连接查询以找出给定用户的哪些令牌分配给游戏或奖品或尚未分配.我的桌子布局是否最佳?还是您对其他布局有建议?

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?

这是我的桌子:

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   |
+-----------+----------+----------+

我想要一个SQL查询，可以获取以下信息:

I would like a SQL query where I can get the following info:

对于用户1，他们有2个令牌-令牌ID 1被分配给游戏1，令牌ID 2被分配给游戏2

For User 1, they have 2 tokens - token id 1 is assigned to game 1 and token id 2 is assigned to game 2

或者对于用户2-他们有4个令牌-将令牌ID 3分配给游戏3，将令牌ID 4分配给奖品1，将令牌ID 5分配给游戏4.

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.

或者对于用户3-他们有3个令牌-令牌ID 6尚未分配，令牌ID 7是奖品2，令牌ID 10尚未分配

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

以此类推.

我想构建一个像这样的MySQL查询字符串

I would like to build a MySQL query string like this

Select **** 
  from ***** 
 Where user_id = 1

我在其中指定用户ID并获取上面的所有信息.

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

这篇关于mysql连接表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！