mysql limit records by group plus inner join multiple tables

Question

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.

Eric · Accepted Answer · 2019-03-13T18:45:51.840

0

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

edited Mar 13 '19 at 18:45

answered Mar 13 '19 at 16:53

Eric

3,165
1
19
25

Please how do i use it am trying to replace mine with your example but it doesn't work. Can you tell me how to use your example please – Peter Mar 13 '19 at 16:56
What's the version of MySQL you use? – Eric Mar 13 '19 at 17:51
Here it is`mysql Ver 15.1 Distrib 10.1.37-MariaDB, for Linux (x86_64) using readline 5.1` and on localhost `10.1.25-MariaDB` – Peter Mar 13 '19 at 18:17
So you should be able to run my query. – Eric Mar 13 '19 at 18:28
Okay from the link you add to your answer i can see that it worked, but you didn't include my join table and distance calculation. How do i use everything together? – Peter Mar 13 '19 at 18:37

mysql limit records by group plus inner join multiple tables

1 Answers1