0

I have a table in mysql of hashtags:

strHash     |  nPersonID
-------------------------
#dogowner   |     1
#catowner   |     1
#dogowner   |     2
#mouseowner |     3
#fish       |     3
#vancouver  |     1
#vancouver  |     3

I'd like to query the table with a MySQL statement and say, I want nPersonID = 1, return a result of all people that share one ore more strHash values.

This will allow me to show similar people when viewing a specific person. (or, people who share similar hash tags - some but not necessarily all)

potashin
  • 44,205
  • 11
  • 83
  • 107
Nathan Leggatt
  • 398
  • 1
  • 6
  • 18
  • no, i know how to get the person 1, I want to say, Get person 1, BUT then the results would include other people who have maching strHash values... so in the example above, asking for person 2 would also return person 1 (because they both have dogowner has a hash, BUT, if I asked for person 1, I would get everyone because person shares hash values with all the other nPersonID rows. – Nathan Leggatt Jun 12 '14 at 06:20

5 Answers5

1

The simplest way to get just a list of people that share hastags with person 1 is this:

SELECT DISTINCT nPersonID
FROM table
WHERE strHash IN (
  SELECT b.strHash
  FROM table b
  WHERE b.nPersonID=1
)

But you can get some little bit of extra info without making things too complex. For example, this query will list the same people, but also give you a quantifier of how similar these people's hashtags are to those person 1:

SELECT nPersonID, COUNT(DISTINCT strHash) AS shared_hashtags
FROM table
WHERE strHash IN (
  SELECT b.strHash
  FROM table b
  WHERE b.nPersonID=1
)
GROUP BY nPersonID
ORDER BY shared_hashtags DESC

This will select all the people that have one or more of the hashtags that person 1 has... including person 1.

The resuling list will be ordered by the number of common hastags... with the most similar people on top (including person 1).

Frazz
  • 2,995
  • 2
  • 19
  • 33
0

Try this SQL:

SELECT DISTINCT t2.nPersonID
FROM table t1
JOIN table t2
  ON t1.strHash = t2.strHash
WHERE t1.nPersonID = 1
hansmei
  • 660
  • 7
  • 17
0

The idea:

  • Get the hashtags of the selected person
  • Get all persons which have similar hashtags
  • Count how many hastags are matching

The query:

SELECT
    S.nPersonId
    , COUNT(1) AS SimilarHashCount
FROM (
        SELECT
            strHash
        FROM
            hashtags
        WHERE
            nNersonID = 1
    ) P
    INNER JOIN (
        SELECT
            strHash
            , nPersonD
        FROM
            hashtags
        WHERE
            nNersonID != 1
    ) S
        ON P.strHash = S.strHash
GROUP BY
    S.nPersonID
ORDER BY
    COUNT(1) DESC

You can limit the result, or you can use the HAVING statement to filter the results

Pred
  • 8,789
  • 3
  • 26
  • 46
0

Many ways to achieve this. I'll do it with following query

SELECT DISTINCT [nPersonID]
  FROM [htag]
  where strHash in (select strHash FROM [htag] where [nPersonID] = 1)

This will output as below

nPersonID
1
2
3

If you wish to get result without the ID '1' (request id) add another condition

SELECT DISTINCT [nPersonID]
  FROM [htag]
  where strHash in (select strHash FROM [htag] where [nPersonID] = 1)
    and [nPersonID] != 1

Output:

nPersonID
2
3

Hope this helps!

Ankit Patel
  • 1,191
  • 1
  • 9
  • 9
0
 SELECT *, COUNT(*) FROM XXX GROUP BY strHash HAVING COUNT(*) > 1

This question has been asked before - How to select non "unique" rows

The answer above belongs to judda (a bit modified)

Community
  • 1
  • 1