CODEWITHSUNDEEP
mysqlsqlsubquerysql-order-by
Nirav Dangi
Nirav Dangi

Reputation: 3647

Subquery - records are not in ordered form

I have three tables:

user: id, name
keyword: id, name
userkeyword: id, user_id, keyword_id

I want to execute query in following way:

Display those users whose keyword/s are matched with the login user's keywords. In the order of maximum number of keyword matched user should display first

e.g : If userA having 4 matched keywords, userB having 8, userC having 1, userD having 6 then the result should be in the order of,

userB
userD
userA
userC

For that I have done with this query (assume login user's id is 1):

select * 
from user 
where id IN (
    select user_id 
    from userkeywords 
    where keyword_id IN (
        select keyword_id 
        from userkeywords 
        where user_id=1) 
    group by user_id 
    order by count(keyword_id) desc) 
    AND id != 1

Here the result is getting perfect but the order is not correct. I have merged two queries in following manner"

 select * 
 from user 
 where id IN (?) 
      AND id!=1

+

 select user_id
 from userkeywords 
 where keyword_id IN (
      select keyword_id 
      from userkeywords 
      where user_id=1) 
 group by user_id 
 order by count(keyword_id) desc

Second query returns user_id in correct order but when I merged both queries, order was changed (wrong).

Hope I have mentioned my query properly with enough detail.

Upvotes: 1

Views: 58

Answers (4)

suchislife
suchislife

Reputation: 1

You can join the tables and use GROUP BY to count the number of keywords that match for each user. You could then sort by the count in descending order to get the users with the maximum number of keyword matches first. You don't really need to use subqueries for this. Take a look:

SELECT u.id, u.name, COUNT(*) as matched_keywords
FROM user AS u
JOIN userkeyword AS uk1 ON u.id = uk1.user_id
JOIN userkeyword AS uk2 ON uk1.keyword_id = uk2.keyword_id
WHERE uk2.user_id = 1
      AND u.id != 1
GROUP BY u.id, u.name
ORDER BY matched_keywords DESC;

Upvotes: 0

Jens
Jens

Reputation: 2075

I would use an inner join to select the correct rows:

SELECT *
FROM user
INNER JOIN (
   SELECT * FROM userkeyword 
   WHERE keyword_id IN (
       SELECT keyword_id 
       FROM userkeyword 
       WHERE user_id=1
   )
) uk 
ON user.id = uk.user_id 
GROUP BY u.id
ORDER BY count(*) DESC;

Upvotes: 0

Paulo Avelar
Paulo Avelar

Reputation: 2140

This should do it. 

select uk.user_id, u.name
from userkeywords uk
left join user u on u.id = uk.user_id
where uk.keyword_id IN ( 
    select keyword_id 
    from userkeywords 
    where user_id=1) 
group by uk.user_id 
order by count(uk.keyword_id) desc) AND uk.user_id != 1

Also, JOIN provides better performance.

Upvotes: 0

Andomar
Andomar

Reputation: 238246

A subquery returns an unordered set, so the order by in a subquery only matters for its limit clause, if there is any. Any database other than MySQL would give an error message for a purely decorative sort order.

There's no way to sort on a column that only exists in the where clause. You'd have to rewrite the query. One option is to replace your in conditions with joins:

select  uk2.name
from    userkeywords uk1
join    userkeywords uk2
on      uk1.keyword_id = uk2.keyword_id
        and uk1.user_id <> uk2.user_id
join    user u2
on      u2.id = uk2.user_id
where   uk1.user_id = 1
group by
        uk2.name
order by
        count(*) desc

Upvotes: 2

Related Questions