Inner Join table with respect to a maximum value
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 ║
╚══════════╩═══════════╩═══════════╩═══════════╩═════════╩═══════╝
rocket_boomerang_19
Starting my own webpage for a startup and am diving into the world of coding.
Updated on July 19, 2022Comments
-
rocket_boomerang_19 almost 2 years
I'm trying to write a MySQL query where I pull a seller's info and her most popular product. This is determined by the product with the most page views, i.e.
MAX(page_views)
.The query below though is just pulling a random product and not the one with the most 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 said,
page_views
is in fact the correct number, but how do I get the other product fields (id, image, title, etc.) to be with respect to the product with the 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 (condensed):
seller.id | shop_name | product.id | pageviews | title | price 1 | miteinda | 2 | 30 | bufanda | $25 3 | newworld | 6 | 6 | ropa | $13
The result should list out sellers information who have been handpicked and their most popular product by pageviews The sellers are ordered by pageviews with a limit of 5 sellers total.