How to a select record from my multiple tables and limit the number of the record from each seller using group by or other method. I know this maybe duplicate question but i have also tried a lot of examples but none of it work in my case.
I have been trying to get this query done for more than 4 days now but still with no luck. Please can anyone assist me i have also included sqlfiddle reference here http://sqlfiddle.com/#!9/7a9d62/5
Below is what i tried doing at last but still not working. And i also did post a question here on stack-overflow but i got no answer How to limit number of record using mysql group by and inner join for multiple tables
SELECT p.product, p.p_id, p.p_store_key, p.p_seller_key, st.store, sl.seller,
SQRT(POW(69.1 * (st.st_lat - 2.922461), 2) + POW(69.1 * (101.639587 - st.st_lng) * COS(st.st_lat / 57.3), 2)) AS distance
FROM dbt_product AS p
INNER JOIN dbt_sellers AS sl
ON p.p_seller_key = sl.s_id
INNER JOIN dbt_stores AS st
ON st.st_id = p.p_store_key
WHERE (
SELECT COUNT(*)
dbt_product AS pl
WHERE pl.p_id = p.p_id
) <= 2
HAVING distance <= 25
ORDER BY sl.s_id DESC
Product Table
product | p_id | p_store_key | p_seller_key
----------|--------|----------------------|----------------
dog | 100 | MT66 | SE100
bird | 101 | MT67 | SE100
cow | 103 | MT66 | SE100
cat | 104 | MT66 | SE100
goat | 105 | QR89 | NK100
cow | 106 | QR89 | NK100
cat | 107 | QR89 | NK100
cat | 108 | GK59 | MB100
rat | 109 | GK15 | MB100
fish | 110 | UI100 | VX100
Product Stores Table
store | st_id | st_lng | st_lat
----------------|-----------|------------|--------------------
SellPoint | MT66 | 00.0000 | 00.0000
Sell days | MT67 | 00.0000 | 00.0000
payNGo | QR89 | 00.0000 | 00.0000
CashPoin | GK15 | 00.0000 | 00.0000
FastSell | VX100 | 00.0000 | 00.0000
Sellers Table
seller | s_id
----------------|-------------------
Peter | SE100
John | NK100
Hana | MB100
Alice | UI100
Expected Result
product | p_id | p_store_key | p_seller_key
---------|--------|---------------|-------------------------------
dog | 100 | MT66 | SE100
bird | 101 | MT67 | SE100
goat | 105 | QR89 | NK100
cow | 106 | QR89 | NK100
cat | 108 | GK59 | MB100
rat | 109 | GK15 | MB100
fish | 110 | UI100 | VX100
Please i really do need help on this, i know is possible to archive but i don't know how to go about it.
You can use ROW_NUMBER()
SELECT *
FROM (
SELECT *
, ROW_NUMBER() OVER(PARTITION BY p_seller_key ORDER BY p_seller_key) AS rn
FROM dbt_product p
) a
WHERE rn <= 2
https://www.db-fiddle.com/f/5A6YnhdQGkBHFthiobYM6B/0
Adding JOIN
SELECT p.*
, SQRT(POW(69.1 * (st.st_lat - 2.922461), 2) + POW(69.1 * (101.639587 - st.st_lng) * COS(st.st_lat / 57.3), 2)) AS distance
FROM (
SELECT *
, ROW_NUMBER() OVER(PARTITION BY p_seller_key ORDER BY p_seller_key) AS rn
FROM dbt_product p
) p
INNER JOIN dbt_sellers sl ON p.p_seller_key = sl.s_id
INNER JOIN dbt_stores st ON st.st_id = p.p_store_key
WHERE rn <= 2
AND SQRT(POW(69.1 * (st.st_lat - 2.922461), 2) + POW(69.1 * (101.639587 - st.st_lng) * COS(st.st_lat / 57.3), 2)) < 25
ORDER BY sl.s_id DESC