My immediate apologies if this has been answered elsewhere. I can't seem to get a working version of this, and I have tried many different things, some from knowledge and others from googling around a bit.
I am using the Haversine formula in my SQL query to order the distance of a few retail stores so I can plot them on a map. I am getting the distances and what not fine, but when I added an option to filter out duplicate, or chain, stores I run into problems. The problems differ based off the different queries I've tried, and I was wondering if someone could point out where I'm going wrong so I can learn from this experience. :D
Things I have tried (note: the lat/lon in these examples are false lat/lon and the lat/lon I get is from another SQL table):
SELECT store_id,col_a,col_b,col_c,store_name,store_number,street_address,apt_suite,city,state_id,zip_code,latitude,longitude,phone_number,phone_extension,fax_number,email_addr,location_direction,open_24_hr,website_url, (3959*acos(cos(radians(12.1234567)) * cos(radians(latitude)) * cos(radians(longitude)-radians(-45.678910.)) + sin(radians(12.1234567)) * sin(radians(latitude)))) AS distance FROM stores WHERE primary_provider_code = '01' OR secondary_provider_code = '01' OR tertiary_provider_code = '01' ORDER BY distance LIMIT 0 , 10
This will get me the basics done. Gets nearest 10 stores, orders by distance, and only selecting the columns I want. Now when someone clicks the "filter same chain" option, I've tried the following:
SELECT store_id,col_a,col_b,col_c,store_name,store_number,street_address,apt_suite,city,state_id,zip_code,latitude,longitude,phone_number,phone_extension,fax_number,email_addr,location_direction,open_24_hr,website_url, (3959*acos(cos(radians(12.1234567)) * cos(radians(latitude)) * cos(radians(longitude)-radians(-45.678910.)) + sin(radians(12.1234567)) * sin(radians(latitude)))) AS distance FROM stores WHERE primary_provider_code = '01' OR secondary_provider_code = '01' OR tertiary_provider_code = '01' GROUP BY store_name ORDER BY distance LIMIT 0 , 10
This does render results, but it literally filters out every same-chain, not just all after one is shown. For example if we have Walgreens, Costco, Wal-Mart, Walgreens, Target, etc... then I only want to show the first Walgreens and filter the second one out. Google searches led me to believe that the GROUP BY clause would work. It removed all Walgreens. In fact, it removed all rows that had duplicates.
I have also tried GROUP BY store_name HAVING COUNT(*) = 1 and >,<,>=,<= variations for the sake of trying.
I don't believe I can do a SELECT DISTINCT since the the other columns are all going to be distinct in themselves, so even if the store_name may have duplicates, all the other rows I'm trying to select are unique. Also, I have tried this and confirmed it does not filter out non unique columns.
Thanks in advance for the help. Note that I've tagged PHP in as well since it is known to interface with SQL and the platform I'm developing on is PHP.
NOTE: I'm not looking for only one store. I am looking for all stores, ordered by distance, grouped and filtering out the duplicate stores. Let's say the unfiltered result is
Wal-Mart
K-Mart
Wal-Mart
Walgreens
Costco
Sams Club
Wal-Mart
Costco
Walgreens
I want to return the filtered result of:
Wal-Mart
K-Mart
Walgreens
Costco
Sams Club
One solution:-
SELECT store_id,col_a,col_b,col_c,stores.store_name,store_number,street_address,apt_suite,city,state_id,zip_code,latitude,longitude,phone_number,phone_extension,fax_number,email_addr,location_direction,open_24_hr,website_url, sub1.distance AS distance
FROM stores
INNER JOIN
(
SELECT store_name, MIN(3959*acos(cos(radians(12.1234567)) * cos(radians(latitude)) * cos(radians(longitude)-radians(-45.678910.)) + sin(radians(12.1234567)) * sin(radians(latitude)))) AS distance
FROM stores
WHERE primary_provider_code = '01' OR secondary_provider_code = '01' OR tertiary_provider_code = '01'
GROUP BY store_name
) sub1
ON stores.store_name = sub1.store_name
AND (3959*acos(cos(radians(12.1234567)) * cos(radians(latitude)) * cos(radians(longitude)-radians(-45.678910.)) + sin(radians(12.1234567)) * sin(radians(latitude)))) = sub1.distance
WHERE primary_provider_code = '01' OR secondary_provider_code = '01' OR tertiary_provider_code = '01'
ORDER BY distance LIMIT 0 , 10
This uses a sub query to get the nearest store for each chain (hence the MIN / GROUP BY in the sub query), then joins that against the stores table to get the full details of the nearest store.
I cant't see an error in your SQL i test it in my Database and get the right result.
SELECT store_id,
col_a,
col_b,
col_c,
store_name,
store_number,
street_address,
apt_suite,
city,
state_id,
zip_code,
latitude,
longitude,
phone_number,
phone_extension,
fax_number,
email_addr,
location_direction,
open_24_hr,
website_url,
(3959*acos(cos(radians(12.1234567)) * cos(radians(latitude)) * cos(radians(longitude)-radians(-45.678910.)) + sin(radians(12.1234567)) * sin(radians(latitude)))) AS distance
FROM stores
WHERE
primary_provider_code = '01' OR
secondary_provider_code = '01' OR
tertiary_provider_code = '01'
GROUP BY GROUP BY store_name
ORDER BY distance LIMIT 0 , 10
Alternativ try this:
SELECT store_id,
col_a,
col_b,
col_c,
DISTINCT(store_name),
store_number,
street_address,
apt_suite,
city,
state_id,
zip_code,
latitude,
longitude,
phone_number,
phone_extension,
fax_number,
email_addr,
location_direction,
open_24_hr,
website_url,
(3959*acos(cos(radians(12.1234567)) * cos(radians(latitude)) * cos(radians(longitude)-radians(-45.678910.)) + sin(radians(12.1234567)) * sin(radians(latitude)))) AS distance
FROM stores
WHERE
primary_provider_code = '01' OR
secondary_provider_code = '01' OR
tertiary_provider_code = '01'
ORDER BY distance LIMIT 0 , 10
If you simply want the distinct store names of the nearest stores, then you can do a DISTINCT on top of your current query, as below:
SELECT DISTINCT STORE_NAME
FROM
(SELECT
STORE_ID,
COL_A,
COL_B,
COL_C,
STORE_NAME,
STORE_NUMBER,
STREET_ADDRESS,
APT_SUITE,
CITY,
STATE_ID,
ZIP_CODE,
LATITUDE,
LONGITUDE,
PHONE_NUMBER,
PHONE_EXTENSION,
FAX_NUMBER,
EMAIL_ADDR,
LOCATION_DIRECTION,
OPEN_24_HR,
WEBSITE_URL,
(3959 *ACOS(COS(RADIANS(12.1234567)) * COS(RADIANS(LATITUDE)) * COS(RADIANS(
LONGITUDE)-RADIANS(-45.678910.)) + SIN(RADIANS(12.1234567)) * SIN(RADIANS(
LATITUDE)))) AS DISTANCE
FROM
STORES
WHERE
PRIMARY_PROVIDER_CODE = '01'
OR SECONDARY_PROVIDER_CODE = '01'
OR TERTIARY_PROVIDER_CODE = '01'
ORDER BY
DISTANCE LIMIT 0 ,
10
) nearest_stores;