Select only duplicates entry with join

Transact-SQL
1

Dear ones ,
I am new to this forum and after having tried them all .... really all,
I decided to write to you hoping someone would have 5 minutes free for me.

My need is simple enough for you. I'll explain the situation to you.

I have three tables:

results (id, idrace, idathlete, etc, etc,)
races (id, name, etc, etc)
athletes (id, name, etc, etc)

then there are other fields that are not interesting right now.

The query I have to do is input two idathete and what I need and '
the display of the only events to which both of them participated.

To give you an example, let's talk about Tennis.

results (1, 11, 21)
Results (2, 12, 21)
Results (3, 12, 22)

races (11, "TENNIS BACK 1")
races (12, "TENNIS BACK 2")

athletes (21, "Bill Clinton")
athletes (22, "Jorge Bush")

so I see the tournament "2" with the participation of both.

The "mother" table results are linked to the other two so:

results, races
---------, ---------
idrace -------------> id

results, athletes
---------, ---------
idathlete -------------> id

apart from the scheme a bit too rough, I can tell you there are constraints
of referential integrity between the three tables or:

results (idathlete) -> Athletes (id)
results (idrace) -> races (id)

So my goal is to show, and not count, only the results
where the two athletes are present as inputs.

Who can help me?
Thank you all !
Greetings
Willy

2

This looks very much like a school assignment - is it?.

I can tell you (in words), how I would solve it:

  • find athlete count, having more than 1, for same race, in results table (now you have idrace)
  • join the above result to the other tables as needed
3

Dear Bitsmed,

No :wink: this is a real problem for giving to my friends a program to showing results when
two of them play at the same event.

It doesn't so easy !

"find athlete..... " Yes but how ? This is a problem because I've one select in one table
(results) that could have some results with the same idrace and for the same idrace
could have more entries and more idathletes. I don't so fluent with sql .

the last join that you suggest me seems to be easy but

Thanks a lot !

4

To solve this, you need to master/read up on:

  • count
  • group by
  • having
  • subselect
  • join