All geek questions in one place

Internal Join table in relation to the maximum value

I am trying to write a MySQL query where I display information about the seller and its most popular product. This is determined by the product with the most page views, i.e. MAX(page_views) .

The query below just pulls out a random product, not one that has more page views.

 "SELECT seller.id, seller.language, seller.shop_name,seller.story, seller.eng_story, product.id, product.image_thumb, product.title, product.eng_title, product.price, MAX(product.page_views) FROM seller INNER JOIN product ON seller.id=product.seller_id WHERE seller.handpicked='y' AND seller.shop_active='y' GROUP BY seller.id ORDER BY product.page_views LIMIT 0,5"

Or better to say, page_views is actually the correct number, but how can I get other product fields (id, image, title, etc.) relative to the product with most page views.

TABLE DATA:

SELLER:

 id | language | shop_Name | story | eng_story | handpicked | active 1 | 1 | mitienda | hola mundo| Hello world | Y | Y 2 | 1 | sisenor | bonita | beautiful | N | Y 3 | 2 | new_world | mi vida | my life | Y | Y

PRODUCTS:

 id | seller_id | image_thumb | title | eng_title | price | page Views 1 | 1 | /images/.. | sombrero | hat | $5 | 10 2 | 1 | /images/.. | bufanda | scarf | $25 | 30 3 | 2 | /images/.. | arte | art | $15 | 15 4 | 3 | /images/.. | joyeria | jewlery | $10 | 1 5 | 2 | /images/.. | canasta | basket | $21 | 13 6 | 3 | /images/.. | ropa | clothes | $13 | 6

Expected Result (Compressed):

 seller.id | shop_name | product.id | pageviews | title | price 1 | miteinda | 2 | 30 | bufanda | $25 3 | newworld | 6 | 6 | ropa | $13

As a result, the data of the sellers that were selected and their most popular product for page views should be indicated. Sellers are sorted by pageviews with a limit of 5 sellers.

+4
source share
1 answer
 SELECT a.ID SellerID, a.Shop_Name, b.ID ProductID, b.pageViews, b.title, b.Price FROM seller a INNER JOIN Products b ON a.id = b.seller_ID INNER JOIN ( SELECT seller_ID, MAX(pageViews) max_view FROM products GROUP BY seller_ID ) c ON b.seller_ID = c.seller_ID AND b.pageViews = c.max_View WHERE a.handpicked = 'Y' AND a.active = 'Y'

OUTPUT

 ╔══════════╦═══════════╦═══════════╦═══════════╦═════════╦═══════╗ ║ SELLERID ║ SHOP_NAME ║ PRODUCTID ║ PAGEVIEWS ║ TITLE ║ PRICE ║ ╠══════════╬═══════════╬═══════════╬═══════════╬═════════╬═══════╣ ║ 1 ║ mitienda ║ 2 ║ 30 ║ bufanda ║ $25 ║ ║ 3 ║ new_world ║ 6 ║ 6 ║ ropa ║ $13 ║ ╚══════════╩═══════════╩═══════════╩═══════════╩═════════╩═══════╝
+7
source

Source: https://habr.com/ru/post/1469145/

More articles:


All Articles