CODEWITHSUNDEEP
mysqlsql
Kuldeep Dangi
Kuldeep Dangi

Reputation: 4452

find records grouped by two columns in mysql

I want to find out all seller who have uploaded products in categories (electronics,clothing,furniture), so for 3 categories there can be 3 row against each seller . tables I have are

1.category{category_id,name},

2.seller {seller_id,username},

3.products{product_id,seller_id,category_id,title}

Note:There can be maximum 3 result (coz I'm searching in 3 categories) against one seller even if he added more than one product in single category

expected result:

**product_id**        **category**           **sellerUsername**
101                     electronics               kuldeep
211                     furniture                 kuldeep
322                     clothing                  kuldeep
167                     electronics               roman
245                     furniture                 roman
247                     clothing                  dangi
246                     furniture                 dangi
..
..

Upvotes: 2

Views: 48

Answers (3)

Mr. Bhosale
Mr. Bhosale

Reputation: 3106

Check Below Code.

        SET @row_number:=0;
        SET @db_names:= '';
        SET @db_names2:= '';

        select product_id,name as category ,username as sellerUsername
        from (
        select a.product_id,  c.name ,b.username,
               @row_number:=CASE WHEN @db_names=username and @db_names2=name THEN @row_number+1 
               ELSE 1 END AS row_number,@db_names:=username AS username2,@db_names2:=name AS name2
        from products as a 
        left join seller as  b on b.seller_id = a.seller_id
        left join category as c on c.category_id = a.category_id
        where name IN ('electronics', 'clothing', 'furniture') 
        )a where row_number < 2
        order by sellerUsername,name;

Output :

enter image description here

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522797

The general solution to your problem is to join the three tables together and then aggregate by seller and category. In my solution, I have arbitraily chosen the max product ID, in the absence of any logic for doing otherwise. The query is slightly tricky, in that we need to additionally join this result again to the category and seller tables to get the human readable category and seller names. The reason for this is the GROUP BY query should ideally be done by ID and not name, since conceivably two categories (or sellers) could have the same name but have different IDs.

SELECT t3.product_id,
       COALESCE(t1.name, 'NA'),
       COALESCE(t2.username, 'NA')
FROM
(
    SELECT MAX(p.product_id) AS product_id,
           c.category_id,
           s.seller_id
    FROM products p
    LEFT JOIN category c
        ON p.category_id = c.category_id
    LEFT JOIN seller s
        ON p.seller_id = s.seller_id
    WHERE c.name IN ('electronics', 'clothing', 'furniture')
    GROUP BY s.seller_id,
             c.category_id
) t1
LEFT JOIN category t2
    ON t1.category_id = t2.category_id
LEFT JOIN seller t3
    ON t1.seller_id = t3.seller_id

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133410

if you need only the matching relation use inner join 

select a.product_id, b.username, c.name
from products as a 
inner join seller as  b on b.seller_id = a.seller_id
inner  join category as c on c.category_id = a.category_id

else use left join 

select a.product_id, b.username, c.name
from products as a 
left join seller as  b on b.seller_id = a.seller_id
left   join category as c on c.category_id = a.category_id

Upvotes: 1

Related Questions