SQL find rows with multiple references

Question

For our online ordering platform, we have a database of streets that our stores deliver to. Based on an export from our POS system.

In the real world there is only one store that can deliver to any exact street address. This is not the case in our database, where street names is figurating on multiple stores.

The table is like this - simplified:

streets

street_id
street_name
street_from
street_to
store_id

Some examples of rows that currently are in the table:

1, "very nice street", 1, 999, 6
2, "very nice street", 1, 999, 10
3, "random street", 1, 53, 10
4, "random street", 2, 64, 10
5, "unique street", 1, 999, 10
6, "unique street", 1, 999, 6

Any idea on how i get all rows, where the same street name is referenced to two or more stores? In the example above i would like the query to return "very nice street" and "unique street".. I don't want "random street" as it's only referenced to store no# 10.

Thus the following SQL will return "random street"

SELECT street_name, COUNT(*)
FROM streets
GROUP BY street_name
HAVING COUNT(*) > 1;

Answer 1

You can achieve this by GROUP BY and HAVING:

SELECT * from streets group by street_name having count(*) > 1

Here's a working sample: SQLFiddle. And here's a short article you might find interesting: The HAVING and GROUP BY SQL clauses.

---

According to your edit, the query that would return the desired results would be:

SELECT street_name, store_id, count(*) 
FROM streets 
GROUP BY street_name
HAVING COUNT(DISTINCT store_id) > 1

Notice the use of DISTINCT store_id. If there was another random street referencing store #10, it wouldn't be getting through because of the DISTINCT. I've included an example in the updated SQLFiddle.