CODEWITHSUNDEEP
mysqlsql
Leon Armstrong
Leon Armstrong

Reputation: 1303

Get related result and order by number of matches

I am having a many to many table like

flyer        tagged     tags
-----------------------------------
id             tid       id
flyer_name     fid       tag_name

And i currently working at the table tagged , what i wanted to do is select all related result and order it by the number of matches.

Lets say i have record ... Table tagged

tid  fid
---------
80    1
80    2
80    3
2     1
2     2
2     6
1     3
1     4
30    5
30    6

First i want to select all flyer which tid is 2 or 80 then group it by fid So it will return fid 1,2,3

And now from 1,2,3 . I wanted to go through all of them by using CASE on the field tid if 2 or 80 then +1 to the variable 'matches' , then sort all fid by number of matches , also return all tags that belong to the flyer

Desired result
fid    matches    tags 
1         2       80,2      #matched with tags 80,2
2         2       80,2      #matched with tags 80,2
3         1       80,1      #matched with tags 80
6         1       2,30      #matched with tags 2

Here is my current mysql code which is not working , i tried to make question as simple as i can , please let me know if you think i should provide more. Thanks !

SELECT fid , tid , MATCHES FROM leon_tagged 
WHERE tid IN (2,80) 
CASE tid
    WHEN '2' then 1
    WHEN '80' then 1
    ELSE 0 END AS MATCHES
GROUP BY fid
ORDER BY MATCHES

Upvotes: 1

Views: 65

Answers (1)

KaeL
KaeL

Reputation: 3659

Try this one. I think there's no need for the CASE statement.

SELECT T.fid
    , COUNT(T.tid) AS matches
    FROM tagged T
    WHERE T.tid IN (2, 80)
    GROUP BY T.fid
    ORDER BY matches DESC, T.fid ASC;

Hey, here's a test in SQL Fiddle

Added tags field:

SELECT T.fid
    , COUNT(T.tid) AS matches
    , GROUP_CONCAT(DISTINCT T.tid) AS tags
    FROM tagged T
    WHERE T.tid IN (2, 80)
    GROUP BY T.fid
    ORDER BY matches DESC, T.fid ASC;

But I'm afraid you will have different desired result:

fid    matches    tags 
1         2       80,2      #matched with tags 80,2
2         2       80,2      #matched with tags 80,2
3         1       80        #matched with tags 80
6         1       2         #matched with tags 2

The last two rows are different from what you want, since you have stated earlier that you only want tags with tid IN (2, 80).

Upvotes: 1

Related Questions