CODEWITHSUNDEEP
mysqljoininner-join
Jithin Varghese
Jithin Varghese

Reputation: 2228

mysql complicated inner join on many to many

I have 7 tables db_category, db_city, db_locality, db_shop, db_shop_locality, db_shop_recommended, db_shop_views.

db_category

category_id    category_name    publish
    1             Apparel          1

db_city

city_id        city_name        publish
   1           bangalore           1

db_locality

locality_id     locality_name     publish
    1            kalyan nagar       1
    2            Madiwala           1

db_shop

shop_id   category_id   city_id   locality_id   shop_name   publish
   1          1            1          1           name         1
   2          1            1          2           name1        1
   3          1            1          1           name2        1
   4          1            1          1           name3        1

db_shop_recommended

recommended_id     category_id     shop_id    priority    publish
     1                 1             1           1           1
     2                 1             2           2           1

db_shop_views

views_id    shop_id    ip_addr    publish
    1          1      127.0.0.1      1
    2          2        ::1          1
    3          4      127.0.0.1      1
    4          4        ::1          1
    5          3        ::1          1

I want to join all the above tables. And the conditions for the join are,

  1. display all the rows from db_shop when publish=1 and join db_category, db_city and db_locality with db_shop

  2. in db_shop_recommended who's priority=1 comes first and so on.

  3. and shop_id not in db_shop_recommended, count shop_id not in db_shop_recommended from db_shop_views who's shop_id got more views.

My result should looks like below,

shop_id   category_name   city_name   locality_name   shop_name
   1         Apparel      bangalore   kalyan nagar     name (result based on `db_shop_recommended` who's priority is more) 
   2         Apparel      bangalore     Madiwala       name1 (result based on `db_shop_recommended` who's priority is more) 
   4         Apparel      bangalore   kalyan nagar     name4 (result based on `db_shop_views` who's view count is more) 
   3         Apparel      bangalore   kalyan nagar     name3 (result based on `db_shop_views`)

I have no idea how to join and count the views. Is it possible to join in above method.

Upvotes: 1

Views: 42

Answers (1)

Jithin Varghese
Jithin Varghese

Reputation: 2228

After lots of searching. Finally i got the answer.

SELECT DISTINCT s.shop_id, c.city_name, l.locality_name, ca.category_name, s.shop_name, s.shop_logo, s.cart_url, s.shop_about FROM db_shop s INNER JOIN db_city c ON c.city_id = s.city_id INNER JOIN db_category ca ON ca.category_id = s.category_id INNER JOIN db_locality l ON l.locality_id = s.locality_id LEFT JOIN (SELECT * FROM db_shop_recommended r ORDER BY r.priority ASC) r1 ON s.shop_id = r1.shop_id LEFT JOIN (select distinct g.shop_id, g.cnt from (select distinct shop_id, count(shop_id) cnt from db_shop_views group by shop_id) g inner join (select max(s.cnt) max_cnt from(select distinct shop_id, count(shop_id) cnt from db_shop_views group by shop_id) s) m on m.max_cnt = g.cnt) v ON s.shop_id = v.shop_id WHERE s.publish = 1

Upvotes: 1

Related Questions