SQL find rows with multiple references
For our online ordering platform, we have a database of streets that are delivered to our stores. Based on 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 with our database, where street names appear in multiple stores.
The table looks like this, simplified:
streets
street_id
street_name
street_from
street_to
store_id
Some examples of strings currently 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 how I get all the lines where the same street name is referenced in 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 a "random street" as it only refers to # 10.
So the following SQL will return "random street"
SELECT street_name, COUNT(*)
FROM streets
GROUP BY street_name
HAVING COUNT(*) > 1;
source to share
You can achieve this with GROUP BY
and HAVING
:
SELECT * from streets group by street_name having count(*) > 1
Here's a working example: SQLFiddle . And here's a short article you might find interesting: HAVING and GROUP BY SQL clauses .
As per your edit, the query that will return the desired results will look like this:
SELECT street_name, store_id, count(*)
FROM streets
GROUP BY street_name
HAVING COUNT(DISTINCT store_id) > 1
Pay attention to the use DISTINCT store_id
. If there was another random street
# 10 link store, it would fail because of DISTINCT
. I have included an example in an updated SQLFiddle .
source to share