如何防止内部联接查询（Postgres）重复 [英] how to prevent duplicates with inner join query (Postgres)

问题描述

我试图了解如何创建查询以基于内部联接过滤掉某些结果。

I am trying to understand how to create a query to filter out some results based on an inner join.

请考虑以下数据：

formulation_batch
-----
id  project_id  name    
1   1           F1.1
2   1           F1.2
3   1           F1.3
4   1           F1.all

formulation_batch_component
-----
id  formulation_batch_id    component_id
1   1                       1
2   2                       2
3   3                       3
4   4                       1
5   4                       2
6   4                       3
7   4                       4

我想选择project_id为1的所有composition_batch记录，并具有component_id为1或2的composition_batch_component。因此，我运行以下查询：

I would like to select all formulation_batch records with a project_id of 1, and has a formulation_batch_component with a component_id of 1 or 2. So I run the following query:

SELECT formulation_batch.* 
FROM formulation_batch 
INNER JOIN formulation_batch_component
ON formulation_batch.id = formulation_batch_component.formulation_batch_id
WHERE formulation_batch.project_id = 1 
    AND ((formulation_batch_component.component_id = 2 
        OR formulation_batch_component.component_id = 1 ))

但是，这将返回重复的条目：

However, this returns a duplicate entry:

1;"F1.1"
2;"F1.2"
4;"F1.all"
4;"F1.all"

是否可以修改此查询，以便仅获取符合条件的唯一composition_batch记录？

Is there a way to modify this query so that I only get back the unique formulation_batch records which match the criteria?

EG：

1;"F1.1"
2;"F1.2"
4;"F1.all"

谢谢

推荐答案

一种方法是使用 distinct ：

SELECT distinct "formulation_batch".* 
FROM "formulation_batch" 
INNER JOIN "formulation_batch_component" 
ON "formulation_batch"."id" = "formulation_batch_component"."formulationBatch_id" 
WHERE "formulation_batch"."project_id" = 1 
    AND (("formulation_batch_component"."component_id" = 2 
        OR "formulation_batch_component"."component_id" = 1 ))

这篇关于如何防止内部联接查询（Postgres）重复的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！