CODEWITHSUNDEEP
mysqlsql
Anthony R
Anthony R

Reputation: 88

Mysql query -- choosing highest rank/ordered subject in table (join)

Scenario:

We have 5 users. (users table) Each user has up to 10 imgs. (image table) These 10 images can be ordered 1 – 10. (image table) Each img can be listed in multiple categories (say there are 5 categories – birds, bees, bunnies, brains, belugas (category table connected to img table via table that stores img_ids and category_ids)

In searching through the categories, say someone chooses bees. The search should find the images in that category that is listed CLOSEST to the #1 img for all users. So if each user has 3 images in the bees category, ordered as numbers 4, 7 & 9, the search should show the 4th as its closest to the number 1.

The results I keep getting are all over the place and almost seems like it is choosing the images via WHEN they were added to the DB.

SELECT i.img_name, i.ordered, a.user_name, c.keyword, c.cat_id
FROM images AS i JOIN artists AS a USING (user_id) 
JOIN img_cat_table AS im USING ( img_id )
JOIN catkeys AS c USING (cat_id)
WHERE ( cat_id = 3) // THE BEES ID # 
    GROUP BY user_id ORDER BY user_name DESC

Upvotes: 1

Views: 287

Answers (3)

jmacinnes
jmacinnes

Reputation: 1609

I'm also not sure if you want to show all of the relevant images in the right order, or only the top one. Assuming that it is the latter situation, you will need to join to a subquery or view that returns the min rank for each user, category:

SELECT i.img_name, i.ordered, a.user_name, c.keyword, c.cat_id
FROM images AS i JOIN artists AS a USING (user_id) 
JOIN img_cat_table AS im USING ( img_id )
JOIN catkeys AS c USING (cat_id)
JOIN (
SELECT user_id, min(img_rank) img_rank
FROM images AS i 
JOIN artists AS a on i.user_id = a.user_id
JOIN img_cat_table AS im on im.img_id = i.img_id
JOIN catkeys AS c on c.cat_id = i.cat_id
WHERE ( cat_id = 3) ) x on x.user_id = a.user_id and x.img_rank = img_rank
WHERE c.cat_id = 3

I'm not sure what the name of the column that holds the image ranking is. I called it img_rank. Hopefully this will give you the idea

Upvotes: 1

Gajendra Bang
Gajendra Bang

Reputation: 3583

though if you can post the table structure and data, that will be great but Here is what I haved tried

SELECT i.img_name, i.ordered, a.user_name, c.keyword, c.cat_id
from (
select img_name, ordered, img_id, user_id from 
images 
group by user_id
order by user_img ) as i
JOIN artists AS a USING (user_id) 
JOIN img_cat_table AS im USING ( img_id )
JOIN catkeys AS c USING (cat_id)
WHERE ( cat_id = 3) // THE BEES ID #

Upvotes: 1

user359040
user359040

Reputation:

Try removing DESC from your ORDER BY clause.

Upvotes: 0

Related Questions