mysqli query Order by priority based on 4 different tables

Question

table_store

store_id  title  slug
1         Jabong jabong
2         Amazon amazon

table_coupon

coupon_id   title           store_id
1           Now 50% Off     1
2           New 2020 Sale   2

table_tag

tag_id title     priority
1      Latest    5
2      Trending  4
3      Hot       3

table_tag_bind
id   tag_id  main_id(is coupon_id)
1    1       1
2    1       2
3    2       1

How can i list the products related to store based on tag priority. But dont repeat the same product if it has more than 1 tag. higher the number more tha priority:

my query: please what is wrong in this query?

SELECT DISTINCT(t1.`coupon_id`), t1.`title` 
FROM `coupon` AS t1 
LEFT JOIN `tag_bind` AS t2 ON t1.coupon_id = t2.main_id 
LEFT JOIN `tag` AS t3 ON t3.id = t2.tag_id  
WHERE t1.`store_id`='1' 
ORDER BY t3.`priority` DESC

Why do you have parentheses around one of the columns? Do you think `DISTINCT` is a function that takes an argument? It's a keyword that applies to the entire SELECT list, not a specific column. — Barmar, Jun 12 '20 at 06:30
yes this is concern i have. because while listing products via any store some the products hav multiple tags and some of haven't. so i just to list all the products based on priority and if they dont have any tag than they will be shown after those products who have tags — Rohit Guleria, Jun 12 '20 at 06:32
See https://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column?rq=1 for how to get the row with the maximum value of a column in a group. The difference here is that the table is actually the `JOIN` between `tag` and `tag_bind`. You can use a VIEW or CTE to treat that as a table, then you can use one of the solutions there. — Barmar, Jun 12 '20 at 06:48

score 1 · Accepted Answer · answered Jun 12 '20 at 07:13

You want to order rows in coupons based on their maximum priority in table tag. I would suggest using a correlated subquery for ordering; this seems to me like the simplest way to phrase your requirement:

select c.coupon_id, c.title 
from coupon as c
where c.store_id = 1
order by (
    select max(t.priority)
    from tag_bind tg 
    inner join tag t on t.id = tg.tag_id
    where tg.main_id = c.coupon_id
) desc

mysqli query Order by priority based on 4 different tables

1 Answers1