0

How do i select from database using GROUP and LIMIT. Am trying to select 2 record from every product, using INNER JOIN on two different table

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 - :getBuyerLatitude), 2) + POW(69.1 * (:getBuyerLongitude - st.st_lng) * COS(st.st_lat / 57.3), 2)) AS distance

FROM product AS p
INNER JOIN seller AS sl
ON p.p_seller_key = sl.s_id

INNER JOIN store AS st
ON st.st_id = p.p_store_key

WHERE ( 
  SELECT COUNT(*) 
    product AS pl
    WHERE pl.p_id = p.p_id
) <= 2

AND p_id  > 1
HAVING distance <= :buyer_distance
ORDER BY sl.s_id DESC

LIMIT 10

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
Peter
  • 1,860
  • 2
  • 18
  • 47
  • So you want to have a max of 2 records per `p_seller_key` if i understand corectly.? This is been asked before on stackoverflow search [top n per group MySQL](https://stackoverflow.com/search?q=top+n+per+group+MySQL) might give you some idea.. The general idea is to use [ranking](https://stackoverflow.com/search?q=MySQL+rank) ( using MySQL 8.0 `ROW_NUMBER()` or simulate it with MySQL user variables) in a inner SQL (subquery) and filter out the rest out the records in the outer SQL with `WHERE rank <= 2` – Raymond Nijland Mar 08 '19 at 11:39
  • @RaymondNijland yes that what i intend to do – Peter Mar 08 '19 at 11:40
  • Can you try making the required SQL yourself with the pointers (links) i've given you? Normally we (vote)close duplicated questions here and this is a duplication unless you stuck and shown a SQL attempt with the new pointers then we are here to help you.. – Raymond Nijland Mar 08 '19 at 11:46
  • @RaymondNijland please see my updated question with attempt i have made – Peter Mar 08 '19 at 11:51
  • Not fan of using co-related subqueries because they are very slow without indexes on large tables.. i almost never use them.. i believe it needs to be `WHERE ( SELECT COUNT(*) product AS pl WHERE pl.p_seller_key = p.p_seller_key AND pl.p_id < p.p_id ) <= 2` instead.. But better you look in the duplicate link at the top in the yellow part there are better and faster methodes in there.. – Raymond Nijland Mar 08 '19 at 12:01
  • @RaymondNijland i have changed it as you suggested but still getting mysql errors – Peter Mar 08 '19 at 12:28
  • Ok iám unable to help futheri don't want to spam the comment system in the question.. Also i can't post a answer because like i expected the question is vote closed.. Try to work out your question with the duplication link.. if you can't make it happen with the duplication link make a new question (mention this one also in the new question very important otherwise it might be flagged as duplicated question again) and explain with you tryed and where your are stuck.. – Raymond Nijland Mar 08 '19 at 12:39

0 Answers0