如何使用postgres内部多个表
如何使用postgres内部多个表
提问于 2019-03-25 00:36:51
我一直试图在我的数据库的多个表内部，但当我内在一个特定的表时，我无法得到正确的结果。
这是我的表
select * from table_1 where table_2_id;
select * from table_2;
select * from table_3 where table_2_id;
select * from table_4 where table_2_id;
这是我的连接查询
select tb1.id, tb1.email, tb4.value1
from table_1 tb1
inner join table_2 tb2 on tb1.table_2_id = tb2.id and tb2.column1_id in (10)
inner join table_3 tb3 on tb2.id = tb3.table_2_id and tb3.column2 in ('E-mail')
inner join table_4 tb4 on tb2.id = tb4.table_2_id and tb4.value1 = 'example@example.com';
那么结果应该只是table_1在table_4中有'example@example.com.br'但我得到table_1中与table_2具有相同id的所有行
我期望的样本数据：
1 | example@example.com | example@example.com
2 | example@example.com | example@example.com
3 | example@example.com | example@example.com
我得到一段时间的样本数据：
1 | anyemail@example.com | example@example.com
2 | example1@example.com | example@example.com
3 | test@example.com | example@example.com
...
有办法解决这个问题吗？
Stack Overflow用户
发布于 2019-03-25 09:47:46
您可以尝试添加额外条件 tb1.email = tb4.value1
select tb1.id, tb1.email, tb4.value1
from table_1 tb1
inner join table_2 tb2 on tb1.table_2_id = tb2.id and tb2.column1_id = 10
inner join table_3 tb3 on tb2.id = tb3.table_2_id and tb3.column2 = 'E-mail'
inner join table_4 tb4 on tb2.id = tb4.table_2_id and tb4.value1 = 'example@example.com' and tb1.email = tb4.value1;
我只会设置inner join join子句，其他条件where会让代码更清晰。
select tb1.id, tb1.email, tb4.value1
from table_1 tb1
inner join table_2 tb2 on tb1.table_2_id = tb2.id
inner join table_3 tb3 on tb2.id = tb3.table_2_id
inner join table_4 tb4 on tb2.id = tb4.table_2_id
WHERE
tb2.column1_id = 10
AND
tb3.column2 = 'E-mail'
AND
tb4.value1 = 'example@example.com'
AND
tb1.email = tb4.value1
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/-100006496
复制相关文章
相似问题
腾讯云开发者
