CODEWITHSUNDEEP
sqloracle-database
user676853
user676853

Reputation:

Select columns with the same value

I'm trying to select customer names with the same zip code but I can't seem to get the correct result. The correct rows that are meant to be returned are 11. I seem to be getting 14 using the following:

SELECT a.customer_first_name, a.customer_last_name, a.customer_zip FROM customers a
UNION
SELECT b.customer_first_name, b.customer_last_name, b.customer_zip FROM customers b

From here I'm kinda stuck. I know that both statements are the same but this is the basis of where I got to.

I was going to use something along the lines of:

WHERE a.customer_zip=b.customer_zip

But of course this doesn't work and is kind of irrelevant.

Customers table:

Customers table

Any help would be much appreciated. If I've missed anything and/or this comes across unclear, then I apologise. Thanks.

Edit: The correct result should be 11 rows (which I can't seem to achieve).

Upvotes: 1

Views: 4880

Answers (2)

Raphaël Althaus
Raphaël Althaus

Reputation: 60493

SELECT customer_first_name, customer_last_name, customer_zip
FROM customers where customer_zip in 
  (SELECT customer_zip FROM customers
   GROUP BY customer_zip
   HAVING COUNT(*) > 1);

Upvotes: 1

Diego
Diego

Reputation: 36146

I would do:

select customer_zip
from customers 
group by customer_zip
having count(customer_zip)>1

edit:

this will give a list of duplicate ZIPs. based on it, you can esily find the customers with those zips with a select * from customers where customer_zip in (previous_query)

Upvotes: 2

Related Questions