SQL Server Data redundancy-CodePudding

I'm trying to find out the pitstop strategy followed by teams in F1 and which strategy on average yields the better winning results. However every time I try to execute the query I just get redundant data.

Dataset Example:

DriverTable

DriverId	DriverRef	DriverNumber
1	Lewis	22
2	Alonso	14
3	Max	1

RaceResult

ResultID	RaceID	DriverID	Number	Position	Laps
1	12	1	22	1	53
1	12	2	14	6	53
1	12	3	1	2	53
2	13	1	22	2	57
2	13	2	14	6	57
2	13	3	1	1	57

Races

RaceID	year	CircuitID	Name
12	2009	1	Monza
13	2013	2	Bahrain Sakhir

PitStops

RaceID	DriverID	Stop	Lap
12	1	1	17
12	1	2	34
12	2	1	14
12	2	2	42
12	3	1	20
12	3	2	37
13	1	1	14
13	1	2	32
13	2	1	12
13	2	2	34
13	3	1	20
13	3	2	42

My desired result table would look something similar to this.

StrategyChoices

DriverRef	RaceID	CircuitID	Name	Stop	Lap
Lewis	12	1	Monza	1	17
Lewis	12	1	Monza	2	34
Max	13	2	Bahrain Sakhir	1	20
Max	13	2	Bahrain Sakhir	2	42

The goal here find out what pit strategy did the winning driver use on a certain track.

SELECT ra.year, ra.name, d.properdriverref, (SELECT DISTINCT re.number FROM results WHERE re.position = 1), p.stop, p.lap, re.position
FROM pit_stops p
JOIN results re ON re.raceId = p.raceId
JOIN races ra ON p.raceId = ra.raceId
JOIN DriversXL d ON p.driverId = d.driverId    
WHERE ra.year >= 2018 AND re.position = 1

Was the code that I used and the data set returned a

DriverRef	RaceID	CircuitID	Name	Stop	Lap
Lewis	12	1	Monza	1	17
Lewis	12	1	Monza	2	34
Max	12	1	Monza	1	17
Max	12	1	Monza	2	34
Alonso	12	1	Monza	1	17
Alonso	12	1	Monza	2	34
Max	13	2	Bahrain Sakhir	1	20
Max	13	2	Bahrain Sakhir	2	42
Lewis	13	2	Bahrain Sakhir	1	20
Lewis	13	2	Bahrain Sakhir	2	42
Alonso	13	2	Bahrain Sakhir	1	20
Alonso	13	2	Bahrain Sakhir	2	42

CodePudding user response：

You are missing an extra join column between pit_stop and results.

It's unclear the point of that subquery, so I have removed it

SELECT
  d.driverref,
  r.raceId,
  r.circuitId,
  ra.year,
  ra.name,
  p.stop,
  p.lap
FROM pit_stops p
JOIN results re ON re.raceId = p.raceId AND re.DriverID = p.DriverID
JOIN races ra ON p.raceId = ra.raceId
JOIN DriversXL d ON p.driverId = d.driverId    
WHERE re.position = 1;

db<>fiddle

RaceID	DriverID	Stop	Lap
12	1	1	17
12	1	2	34
12	2	1	14
12	2	2	42
12	3	1	20
12	3	2	37
13	1	1	14
13	1	2	32
13	2	1	12
13	2	2	34
13	3	1	20
13	3	2	42

RaceID	DriverID	Stop	Lap
12	1	1	17
12	1	2	34
12	2	1	14
12	2	2	42
12	3	1	20
12	3	2	37
13	1	1	14
13	1	2	32
13	2	1	12
13	2	2	34
13	3	1	20
13	3	2	42

RaceID	DriverID	Stop	Lap
12	1	1	17
12	1	2	34
12	2	1	14
12	2	2	42
12	3	1	20
12	3	2	37
13	1	1	14
13	1	2	32
13	2	1	12
13	2	2	34
13	3	1	20
13	3	2	42