• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 120
  • Last Modified:

Query to give me Companies with multiple addresses

A Table COMPANIES has two fields Company_Name and Address.
Some companies have more than one address.
I need a query to give me all the companies with more than one unique address please.
0
Fritz Paul
Asked:
Fritz Paul
  • 2
  • 2
1 Solution
 
Dale FyeCommented:
SELECT Company_Name, Address
FROM [Companies]
GROUP BY [Company_Name], Address
HAVING Count(*) > 1
0
 
mbizupCommented:
I don't think you'll get quite the right results grouping on both Company_Name and Address.

Try this:


SELECT Company_Name, Count(Address)
FROM Companies
GROUP BY Company_Name
HAVING Count(Address) > 1

Open in new window

0
 
mbizupCommented:
... and if you need to display BOTH the company name and the address:

SELECT c.Company_Name, c.Address
FROM Companies c INNER JOIN
(SELECT Company_Name, Count(Address)
FROM Companies
GROUP BY Company_Name
HAVING Count(Address) > 1) q
ON q.Company_Name = c.Company_Name

Open in new window

0
 
Fritz PaulAuthor Commented:
Thanks that worked.
0
 
Dale FyeCommented:
Actually, I don't think mine or Miriam's will do precisely what you asked for, although her response might give you the result you were actually looking for.

1.  My first try would give you the Company/Addresss combinations where there are more than one of that combination of Company and address.
2.  Miriam's first try (the one you accepted) would simply identify all of the companies that have more than one address in the table, these may not be distinct addresses, which is what you asked for, but may not really be what you meant.

To get what you asked for: I need a query to give me all the companies with more than one unique address you need to first identify the unique combinations of [Company_Name] and Address.  Then, you need to identify the ones where there is more than one address for each company, so I would use:

SELECT Temp.[Company_Name]
FROM (
SELECT DISTINCT Company_Name, Address
FROM [Companies]
) as Temp
GROUP BY Temp.Company_Name
HAVING Count(Temp.[Address]) > 1
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now