Hi I have three tables customers, contacts and order. I need to check if there are duplicates (wrongly entered orders) in the table orders. If yes I need to return the first and last name from table customer, email from table contact and finally order and order id.
I want to check these duplicates by finding more orders with the same order_id.
You need to join this query also:
SELECT order_id, COUNT(*) counter FROM orders GROUP BY order_id HAVING counter > 1
which returns the duplicate order_ids and there is no need for additional aggregation:
SELECT DISTINCT c1.first_name, c1.last_name, c2.email, o.order_id, o.item, t.counter
FROM orders o
INNER JOIN (SELECT order_id, COUNT(*) counter FROM orders GROUP BY order_id HAVING counter > 1) t
ON t.order_id = o.order_id
LEFT JOIN customers c1 ON o.customer_id = c1.customer_id
LEFT JOIN contacts c2 ON o.customer_id = c2.customer_id
What you are describing sounds like:
having count(distinct o.order_id) > 1
EDIT:
You have a very strange data model with an orders table and a non-unique order_id in it. Usually this is split into two tables, such as orders and orderlines.
In any case, you seem to want something like:
select o.order_id
from orders o
group by o.order_id
having count(*) > 1;
You can use this as a subquery and join in other information, such as about customers.
You can use a window count to identify orders whose order_id occurs more than once in the orders table, then join the other tables:
select co.firstname, co.lastname, cu.email, o.order_id, o.item, o.cnt
from (
select o.*, count(*) over(partition by oder_id) as cnt
from orders o
) o
inner join customers cu on cu.customer_id = c.customer_id
inner join contacts co on co.customer_id = c.customer_id
where o.cnt > 1