从两个不同的表中选择所有数据

I want to display banned users and IP's but they are in two different tables. I have table for website users and that table contains column 'ban' and another table for banned IP's. So I want something like this:

我想显示禁用的用户和IP,但它们位于两个不同的表中。我有网站用户的表,该表包含列'禁止'和另一个禁止IP的表。所以我想要这样的东西:

SELECT

ipban.ip, ipban.admin, ipban.reason

ipban.ip,ipban.admin,ipban.reason

AND

users.username WHERE ban=1

users.username WHERE ban = 1

(This query isn't correct, I wrote that just to explain my problem)

(这个查询不正确,我写的只是为了解释我的问题)

And then display that in a table. So, how to do that?

然后在表格中显示。那么,该怎么做?

3 个解决方案

#1

Use UNION, something like this:

使用UNION,如下所示:

(SELECT NULL, ipban.ip, ipban.admin, ipban.reason FROM ipban)
UNION ALL
(SELECT users.username,NULL,NULL,NULL FROM users WHERE ban=1)

I assume this two tables doesn't have common field to join on.

我假设这两个表没有共同的字段来加入。

#2

SELECT ipban.ip, ipban.admin, ipban.reason, users.username FROM ip, users WHERE users.somekey = ipban.somekey and users.ban = 1

Assuming ban is in the users table.

假设禁止在用户表中。

You can also connect the tables using INNER JOIN:

您还可以使用INNER JOIN连接表:

SELECT ipban.ip, ipban.admin, ipban.reason, users.username FROM users INNER JOIN ip ON users.somekey = ip.somekey WHERE users.ban = 1

To get all columns from both tables, use

要从两个表中获取所有列,请使用

SELECT * FROM users INNER JOIN ip ON users.somekey = ip.somekey WHERE users.ban = 1

#3

Select ipban.ip,ipban.admin,ipban.reason,users.username from ipban Inner Join users on common attribute (here you should provide a column which is in both the tables) where ban=1;

选择ipban.ip,ipban.admin,ipban.reason,users.username来自ipban Inner加入用户的公共属性(这里你应该提供一个列在两个表中)ban = 1;

#1