I have a three tables namely profile, academic,payment and these tables having two same columns that are username and status.
我有三个表,即个人资料,学术,付款和这些表有两个相同的列,用户名和状态。
my problem is how to select username from the tables where status=1 in all the tables
我的问题是如何从所有表中status = 1的表中选择用户名
1 个解决方案
#1
Typically it works like this:
通常它的工作原理如下:
SELECT * FROM profile
LEFT JOIN academic ON profile.username=academic.username
LEFT JOIN payment ON profile.username=payment.username
WHERE profile.status=1 AND academic.status=1 AND payment.status=1
As a note having username
as a key is usually a bad thing, often super bad since if someone's able to change their name you need to update N other tables. You may have a circumstance where you forget to update one or more tables, then subsequently someone registers with the former name and "inherits" this data.
因为将用户名作为密钥的注释通常是一件坏事,通常非常糟糕,因为如果有人能够更改其名称,则需要更新N个其他表。您可能遇到忘记更新一个或多个表的情况,然后有人注册了以前的名称并“继承”了这些数据。
It's also typically very inefficient to use a string INDEX
key when a user_id
integer value would suffice.
当user_id整数值足够时,使用字符串INDEX键通常也是非常低效的。
#1
Typically it works like this:
通常它的工作原理如下:
SELECT * FROM profile
LEFT JOIN academic ON profile.username=academic.username
LEFT JOIN payment ON profile.username=payment.username
WHERE profile.status=1 AND academic.status=1 AND payment.status=1
As a note having username
as a key is usually a bad thing, often super bad since if someone's able to change their name you need to update N other tables. You may have a circumstance where you forget to update one or more tables, then subsequently someone registers with the former name and "inherits" this data.
因为将用户名作为密钥的注释通常是一件坏事,通常非常糟糕,因为如果有人能够更改其名称,则需要更新N个其他表。您可能遇到忘记更新一个或多个表的情况,然后有人注册了以前的名称并“继承”了这些数据。
It's also typically very inefficient to use a string INDEX
key when a user_id
integer value would suffice.
当user_id整数值足够时,使用字符串INDEX键通常也是非常低效的。