Reputation: 1
I have a table of shops with some chains among them. When I do a proximity search, I want only the closest shop of each chain to be included in the result. However, I want all shops from the non-chains to be found.
My idea is to assign a group to the chain shops and then filter them in the SQL - but how?
shop_id | shop | shop_lat | shop_lon | chaingroup |
---|---|---|---|---|
1 | SHOP1 | 55.8 | 9.4 | NULL |
2 | SHOP2 | 55.9 | 9.5 | 2 |
3 | SHOP3 | 55.1 | 9.6 | NULL |
4 | SHOP4 | 55.2 | 9.7 | 3 |
5 | SHOP5 | 55.3 | 9.8 | NULL |
6 | SHOP6 | 55.4 | 9.9 | 2 |
7 | SHOP7 | 55.5 | 10.0 | NULL |
8 | SHOP8 | 55.6 | 10.1 | 5 |
9 | SHOP9 | 55.7 | 10.2 | NULL |
10 | SHOP10 | 55.8 | 10.3 | 5 |
My SQL:
SELECT shop, ((ACOS(SIN(53.58393 * PI() / 180) * SIN(shop_lat * PI() / 180) + COS(53.58393 * PI() / 180) * COS(shop_lat * PI() / 180) * COS((10.01413 - shop_lon) * PI() / 180)) * 180 / PI()) * 60 * 1.150779448 * 1.609344) as distance, chaingroup
FROM shops
GROUP BY shop_id
HAVING distance <= 10
ORDER BY distance ASC
Result should be:
shop | distance | chaingroup |
---|---|---|
SHOP1 | 1.0 | NULL |
SHOP2 | 2.0 | 2 |
SHOP3 | 3.0 | NULL |
SHOP4 | 4.0 | 3 |
SHOP5 | 5.0 | NULL |
SHOP7 | 7.0 | NULL |
SHOP8 | 8.0 | 5 |
SHOP9 | 9.0 | NULL |
As you can see, SHOP10 is not listed because SHOP8 from chain no. 5 has already been found.
Any suggestions?
Upvotes: 0
Views: 51