1 | initial version |
To remove duplicate entries from a PostgreSQL JOIN table, you can use the DISTINCT keyword in the SELECT statement to eliminate identical rows, or you can use the GROUP BY clause to group the table by a specific column and remove the duplicates.
For example, suppose you have a JOIN table named "orders" that contains duplicate entries:
SELECT * FROM orders;
id | customer_id | product_id | quantity
----+-------------+------------+---------
1 | 101 | 201 | 2
2 | 102 | 202 | 3
3 | 101 | 201 | 1
4 | 103 | 203 | 4
To remove the duplicates, you can use the following query:
SELECT DISTINCT * FROM orders;
This query will return:
id | customer_id | product_id | quantity
----+-------------+------------+---------
1 | 101 | 201 | 2
2 | 102 | 202 | 3
3 | 101 | 201 | 1
4 | 103 | 203 | 4
Alternatively, you can use the GROUP BY clause to group the table by the customerid and productid columns and sum the quantity:
SELECT customer_id, product_id, SUM(quantity) FROM orders GROUP BY customer_id, product_id;
This query will return:
customer_id | product_id | sum
------------+------------+-----
101 | 201 | 3
102 | 202 | 3
103 | 203 | 4
In this case, the duplicates have been removed, and the total quantity for each customer and product is shown.