CODEWITHSUNDEEP
sql-server
Ski Pro
Ski Pro

Reputation: 37

Finding common rows in SQL

SQL

I have a list of teams and who they played, and the score of a game.

Sample data:

rid gamedate    hid home    Home Score  vid visitor Visitor Score
------------------------------------------------------------------    
183 08/31/2017  4   Aliso           3   15  Beck        2
261 09/20/2017  4   Aliso           12  137 Mission     2
306 09/19/2017  4   Aliso           10  194 San         9
211 08/25/2017  25  Burrou          14  137 Mission     7
161 09/05/2017  25  Burrou          16  15  Beck        18
203 08/26/2017  25  Burrou          6   388 Pal         20
187 08/31/2017  7   Martin Luther   3   15  Beck        2

What I am trying to do is create a query to select 'home' teams that have a 'visitor' team in common. Meaning, teams that have played against a similar opponent.

I have tried intersect, but am not quite clear how to use it in this unique situation. The end result would look like this:

rid gamedate    hid home    Home Score  vid visitor Visitor Score
-----------------------------------------------------------------    
183 08/31/2017  4   Aliso           3   15  Beck        2
187 08/31/2017  7   Martin Luther   3   15  Beck        2
161 09/05/2017  25  Burrou          16  15  Beck        18
261 09/20/2017  4   Aliso           12  137 Mission     2
211 08/25/2017  25  Burrou          14  137 Mission     7

After I am able to discern what teams have played a similar team with the results, I think I can take it from there to work with the data.

I am completely new to the though of using intersect.

Thank you for taking a moment to look at this.

Upvotes: 1

Views: 40

Answers (1)

Juan Ruiz de Castilla
Juan Ruiz de Castilla

Reputation: 974

Using intersect:

Using a join and anything else:

select d.* from
(
    select a.rid, a.hid,a.home,a.vid,a.visitor from matches a
    inner join matches b
    on a.vid = b.vid
    group by a.rid,a.hid,a.home,a.vid,a.vid,a.visitor
    having count(*) > 1
) c
inner join matches d
on c.rid = d.rid

Upvotes: 1

Related Questions