CODEWITHSUNDEEP
sql
Callum
Callum

Reputation: 640

SQL Merging 2 matching rows into one

While going through test papers for upcoming exams me and my group have stumped by a question.

"Write a SQL query which will return a table which contains all pairs of results with the same time. Your table should list the id of each competitor, the two event ids and the time."

For the following table:

compid eventid duration
1            1      150
1            2      167
1            3      133
2            2      145
2            3      182
3            3      179
3            1      179

I was going for something along the lines of:

SELECT r1.compid, r1.duration, r1.eventid, r2.eventid
FROM Results r1, (
    SELECT eventid
    FROM Results r2
    WHERE r1.eventid = r2.eventid AND r1.compid = r2.compid AND r1.duration = r2.duration
) r2;

But i can't seem to reference the r1 table from within the nested table.

If anyone could give any direction on what the premise of the solution would be or how to get around this problem, that would be great.

Upvotes: 1

Views: 85

Answers (2)

Callum
Callum

Reputation: 640

I didn't realise you could join a table with itself. But it was pretty easy after that. Cheers. Solution for anyone who is interested: 

SELECT r1.compid, r1.eventid, r2.eventid, r1.duration
FROM Results r1, Results r2
WHERE r1.compid = r2.compid AND r1.duration = r2.duration AND r1.eventid != r2.eventid
GROUP BY r1.duration

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269553

You are phrasing your answer as a correlated subquery, and then putting the join conditions in the subquery.

Correlated subqueries are simply not allowed in from clauases.

However, it is easy to phrase this as a simple self-join, with the right conditions.

I'll let you work out the particular query.

By the way, you have logic that says that the event_id is the same. Perhaps this is implied by the question, but it is not explicitly stated.

Upvotes: 1

Related Questions