Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Inner Join table with respect to a maximum value

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.

like image 652
rocket_boomerang_19 Avatar asked Mar 14 '13 05:03

rocket_boomerang_19


People also ask

How do I find the maximum value of two tables in SQL?

select id from T1 where price in( select max(price) from( select max(price) as price from T1 union select max(price) as price from T2 union select max(price) as price from T3 ) temp ) union select id from T2 where price in( select max(price) from( select max(price) as price from T1 union select max(price) as price from ...

What is the maximum table one can join in SQL?

The maximum number of tables that can be referenced in a single join is 61.

Can inner join have more rows?

Inner Join can for sure return more records than the records of the table. Inner join returns the results based on the condition specified in the JOIN condition. If there are more rows that satisfy the condition (as seen in query 2), it will return you more results.

Does inner join match NULL values?

A join that displays only the rows that have a match in both joined tables. Columns containing NULL do not match any values when you are creating an inner join and are therefore excluded from the result set.


1 Answers

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'
  • SQLFiddle Demo

OUTPUT

╔══════════╦═══════════╦═══════════╦═══════════╦═════════╦═══════╗
║ SELLERID ║ SHOP_NAME ║ PRODUCTID ║ PAGEVIEWS ║  TITLE  ║ PRICE ║
╠══════════╬═══════════╬═══════════╬═══════════╬═════════╬═══════╣
║        1 ║ mitienda  ║         2 ║        30 ║ bufanda ║ $25   ║
║        3 ║ new_world ║         6 ║         6 ║ ropa    ║ $13   ║
╚══════════╩═══════════╩═══════════╩═══════════╩═════════╩═══════╝
like image 81
John Woo Avatar answered Sep 26 '22 19:09

John Woo