CODEWITHSUNDEEP
sqlsql-server
Thomas Rothen
Thomas Rothen

Reputation: 13

Finding group matches by entry in a table

Let's have a table called Player, which shows the favorite sport of a person : Player (name, favoriteSport) So it can have entries like

Josh | basketball
Josh | swimming
Leo  | swimming
Leo  | basketball
Kate | hockey

I am trying to find in this table all other people who have the exact same favorite sports as Josh.For example Josh favorite sports are (basketball,swimming) which are exactly the same favorite sports of Leo, while Kate doesn't have the same.l

I am able to group the entries by person but the problem I can't solve is how to checked if the grouped entries are equal to some other person entries? Any ideas?

Upvotes: 1

Views: 47

Answers (5)

Venkataraman R
Venkataraman R

Reputation: 12959

You can achieve this by assigning rank for each sport and add the rank.

Thanks to @Yitzhak Khabinsky for the table creation script.

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, name VARCHAR(20), favoriteSport VARCHAR(20));
INSERT INTO @tbl (name, favoriteSport) VALUES
('Josh', 'basketball'), ('Josh', 'swimming'),
('Leo', 'swimming'), ('Leo', 'basketball'),
('Kate', 'hockey');

;
;with cte_sportRank as
(
select row_number() over(order by favoriteSport) as rnk, favoriteSport 
FROM (SELECT DISTINCT favoriteSport from @tbl) as t
), cte_playerfavRank as
(
SELECT t.Name, sum(c.rnk) as srnk
FROM @TBL as t
LEFT OUTER join cte_sportRank  as c
on c.favoriteSport = t.favoriteSport
group by t.name
)
SELECT Name
from cte_playerfavRank 
where srnk = (SELECT srnk from cte_playerfavRank where name = 'Josh')
and name <> 'Josh'
Name
Leo

Upvotes: 0

Yitzhak Khabinsky
Yitzhak Khabinsky

Reputation: 22182

One more approach by using XQuery and Quantified Expressions.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, name VARCHAR(20), favoriteSport VARCHAR(20));
INSERT INTO @tbl (name, favoriteSport) VALUES
('Josh', 'basketball'), ('Josh', 'swimming'),
('Leo', 'swimming'), ('Leo', 'basketball'),
('Kate', 'hockey');
-- DDL and sample data population, end

DECLARE @name VARCHAR(20) = 'Josh'
    , @source NVARCHAR(MAX);

SET @source = TRY_CAST((SELECT favoriteSport AS r
FROM @tbl
WHERE name = @name
FOR XML PATH(''), TYPE, ROOT('source')) AS NVARCHAR(MAX));

SELECT p.name 
    , TRY_CAST('<root>' + @source + 
    TRY_CAST((
    SELECT favoriteSport AS r
    FROM @tbl AS c
    WHERE c.name = p.name
    FOR XML PATH(''), TYPE, ROOT('target')
    ) AS NVARCHAR(MAX)) + '</root>' AS XML)
    .value('every $x in /root/source/r[text()]/text()
            satisfies ($x = (/root/target/r[text()]/text())
           and (count(/root/source/r[text()]) eq count(/root/target/r[text()])))', 'BIT') AS result
FROM @tbl AS p
WHERE name <> @name
GROUP BY p.name;

Output

+------+--------+
| name | result |
+------+--------+
| Kate |      0 |
| Leo  |      1 |
+------+--------+

Upvotes: 1

Stu
Stu

Reputation: 32579

Here's one possible solution, provided you are using at least Sql Server 2017 you can use string_agg to combine the sports and then compare the aggregates

  with favorites as (
    select name, string_agg(favoritesport,'') within group (order by favoritesport asc) agg
    from t
    group by name
  )
  select name
  from favorites
  where agg=(select agg from favorites where name='josh')

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269513

You can use:

with p as (
      select p.*, count(*) over (partition by name) as num_sports
      from player p
     )
select p.name
from p left join
     p pj
     on p.sport = pj.sport and 
        p.num_sports = pj.num_sports and
        pj.name = 'Josh'
group by p.name
having count(*) = count(pj.name);

The idea is to join on the sport, but with some conditions:

  • The join makes sure that all sports for a given name are included.
  • The num_sports condition makes sure that both have the same number of sports.
  • The having clause makes sure that all sports match.

Upvotes: 0

David Traver
David Traver

Reputation: 150

When I need to do this I usually join the table on itself:

SELECT p2.name
FROM Player p1, Player p2
WHERE p1.favoriteSport = p2.favoriteSport
AND p1.name = 'Josh'

Upvotes: 0

Related Questions