return items that are within 5km and joining 4 different table mysql?

Question

How do i show items that are within a 5 kilometer away and below? I have 4 database tables which contain informations.

The first table seller_table conatine seller informations

The second table location_table contains the areas within the country/city seller has a store.

The third table item_table contains the product that is available in seller stores.

The last table item_setting_table contains item setting per store

CREATE TABLE `seller_table` (
    `seller_name` VARCHAR(50) NULL DEFAULT NULL,
    `seller_id` VARCHAR(50) NULL DEFAULT NULL,
    `seller_country` VARCHAR(50) NULL DEFAULT NULL
);

INSERT INTO `seller_table` (`seller_name`, `seller_id`, `seller_country`) 
VALUES ('Peter', 'PP1', 'MY'),  
('John', 'JJ6', 'MY'),  
('Paul', 'PL9', 'SG');

CREATE TABLE `location_table` (
    `loc_name` VARCHAR(50) NULL DEFAULT NULL,
    `loc_id` VARCHAR(50) NULL DEFAULT NULL,
    `loc_lat` VARCHAR(500) NULL DEFAULT NULL,
    `loc_lng` VARCHAR(500) NULL DEFAULT NULL,
    `loc_seller_id` VARCHAR(500) NULL DEFAULT NULL
);

 INSERT INTO `location_table` (`loc_name`, `loc_id`, `loc_lat`, `loc_lng`, `loc_seller_id`) 
 VALUES ('Office CYB', '1A', '2.908810', '101.651398', 'PP1'),  
 ('Office NLI', '1B', '2.725806', '101.942375', 'PP1'),
 ('Outlet S', '3A', '3.051953', '101.671455', 'JJ6');

CREATE TABLE `item_table` (
    `item_name` VARCHAR(50) NULL DEFAULT NULL,
    `item_id` VARCHAR(50) NULL DEFAULT NULL,
    `item_store_ids` VARCHAR(100) NULL DEFAULT NULL,
    `item_seller` VARCHAR(50) NULL DEFAULT NULL
);

 INSERT INTO `item_table` (`item_name`, `item_id`, `item_store_ids`, `item_seller`) VALUES ('Java', '1', '1A, 1B ', 'PP1'), 
  ('PHP', '2', '1A', 'PP1'), 
  ('CSS', '3', '1B', 'PP1'), 
  ('SQL', '4', '1B', 'PP1'),
  ('Python', '5', '3A', 'JJ6'),
  ('HTML', '6', '1A', 'PP1'),
  ('VUE', '7', '1A', 'PP1');


CREATE TABLE `item_setting_table` (
    `set_item_id` VARCHAR(50) NULL DEFAULT NULL,
    `set_item_status` int(1) NOT NULL DEFAULT 1,
    `set_item_store_id` VARCHAR(100) NULL DEFAULT NULL
);

INSERT INTO `item_setting_table` (`set_item_id`, `set_item_status`, `set_item_store_id`) 
VALUES ('1', 1, '1A'), 
  ('2', 1, '1A'), 
  ('3', 1, '1B'), 
  ('4', 1, '1B'),
  ('5', 1, '3A'),
  ('6', 1, '1A'),
  ('7', 0, '1A');

What i actually want to do is to show all product to users within 5.5 kilometer away or less in same country and city, by joining the both 4 tables to check the store location from 'location_table', item status from item_setting_table, seller country from seller_table and items from item_table

I have tried the code but it doesn't return any data or show any error

SET @latitude = 2.924860, @longtitude = 101.636941; /*IN RANGE*/
#SET @latitude = 2.802455, @longtitude = 101.79887110000004; /*IN RANGE*/
#SET @latitude = 3.798494, @longtitude = 101.43447100000003; /*OUT OF RANGE*/
SET @KM = 5.5;

SELECT seller.seller_name,loc.loc_name, item.item_name, 
3956 * 2 * ASIN(SQRT(POWER(SIN((loc.loc_lat - ABS(@latitude)) * PI()/180 / 2), 2) + COS(loc.loc_lng * PI()/180 ) * COS(ABS(@latitude) * PI()/180)  * POWER(SIN((loc.loc_lng - @longtitude) * PI()/180 / 2), 2))) AS distance

FROM item_table item
INNER JOIN seller_table seller
ON item.item_seller = seller.seller_id

INNER JOIN location_table loc
ON loc.loc_id = item.item_store_ids

INNER JOIN item_setting_table sett
ON sett.set_item_id = item.item_id
AND sett.set_item_store_id = item.item_store_ids

WHERE seller.seller_country = "MY"
AND sett.set_item_status = 1
HAVING distance <=  @KM 
OR distance IS NULL
ORDER BY distance IS NULL DESC, distance DESC
LIMIT 100

I created a fiiddle http://sqlfiddle.com/#!9/0f6ed6/5/0

Please i really do need help or if have a better way of doing this i will appreciate that

Why are you storing Latitude and Longitude numerical values as `varchar` ? Check this question and its answers: https://stackoverflow.com/questions/12504208/what-mysql-data-type-should-be-used-for-latitude-longitude-with-8-decimal-places — Madhur Bhaiya, Aug 29 '19 at 10:06
On another note, your query is working fine and I cannot reproduce your issue: https://www.db-fiddle.com/f/8P8nkRuqLcQkGEwibd4HKw/0 — Madhur Bhaiya, Aug 29 '19 at 10:08
@MadhurBhaiya please don't use use `ON item.item_seller = seller.seller_id` — Peter, Aug 29 '19 at 10:13
Works fine for me. Fix the data types, and provide the EXPLAIN for the given query. Incidentally, I have a function (geo_distance_km) which reduces the amount of code needed in such queries. — Strawberry, Aug 29 '19 at 10:29
In your item_table, you still have multiple store ids. ```('Java', '1', '1A, 1B ', 'PP1')``` — Dark Knight, Aug 29 '19 at 10:38
@Strawberry From your query, the first location which is Office CYB returns `PHP, HTML` and from Office NLI returns `SQL, CSS`. But what i actual want is to return all item in both location and control the availability from `setting table` `PHP,CSS,SQL,HTML` except `VUE` which the status is 0. — Peter, Aug 29 '19 at 10:41
@JitendraYadav that how i tried before to implement it, store ids in array. But it didn't work. that why i am trying another method. — Peter, Aug 29 '19 at 10:43

return items that are within 5km and joining 4 different table mysql?

0 Answers0